SQL convert Rows to columns without pivot-CodePudding

I have seen several posts on this many of which suggest Pivot. That might be a good solution but my SSMS is 2016 which dose not support Pivot. I am trying to mimic a MS Access report that used VBA to format the columns. I have been working on this for a year and I am still no closer to solving it, here is the Problem.
My data is stored in a table in rows that are grouped by PC_Pack_Id. I added the ColNum field to indicate what column the data goes in. Didn't know if that would help. I included a script to create a Input table.
The output needs to have 9 columns (See example). Column 1 will always be the PC_Pack_Id. RET_ENV always goes in Column 8, RET_ENV always goes in COLUMN 9. Missing Columns Should have a header of "INSERT". The headers should be the same for all the INPUT rows but will not always be the same every time it runs. I am thinking read the table once to get the header row data. Once you have a header row then a while loop to update the Columns. I am just guessing. Any guidance would be greatly appreciated. Thanks

Input Record Layout--
PC_Pkg_Id Char(3) The Group field
ColNum Int The column across they need to appear in
PC_CompType Char(20) Column Header
PC_CompName Char(20) Column value

output Layout--

DROP TABLE IF EXISTS [dbo].[CompTable]

CREATE TABLE [dbo].[CompTable] (    [PC_Pkg_Id] [varchar](3) NOT NULL,  [ColNum] [int] NOT NULL,    [PC_CompType] [varchar](20) NULL,   [PC_CompName] [varchar](20) NULL, ) ON [PRIMARY]

INSERT INTO [dbo].[CompTable]
           (
            [PC_Pkg_Id]
           ,[ColNum]
           ,[PC_CompType]
           ,[PC_CompName]
           )
     VALUES  ('GNL','1','PAPER','FK212') ,('GNL','2','LETTER','FK172') ,('GNL','3','BROCHURE','FK140 R11/21') ,('GNL','4','BROCHURE','FK213 R11/21') ,('GNL','7','RET_ENV','E2243 R2018') ,('GNL','8','OUT_ENV','E6058') ,('GNM','1','PAPER','FK212') ,('GNM','2','LETTER','FK172') ,('GNM','3','BROCHURE','FK140(50) R11/21') ,('GNM','4','BROCHURE','FK213 R11/21') ,('GNM','7','RET_ENV','E2243 R2018') ,('GNM','8','OUT_ENV','E6058') ,('GNP','1','PAPER','FK212') ,('GNP','2','LETTER','FK172') ,('GNP','3','BROCHURE','FK140 R11/21') ,('GNP','4','BROCHURE','FK213(28) R7/22') ,('GNP','7','RET_ENV','E2243 R2018') ,('GNP','8','OUT_ENV','E6058') ,('GNQ','1','PAPER','FK212') ,('GNQ','2','LETTER','FK172') ,('GNQ','3','BROCHURE','FK213(09) R7/22') ,('GNQ','4','BROCHURE','FK140(09) R11/21') ,('GNQ','7','RET_ENV','E2243 R2018') ,('GNQ','8','OUT_ENV','E6058') ,('GNR','1','PAPER','FK212') ,('GNR','2','LETTER','FK172') ,('GNR','3','BROCHURE','FK140(15) R11/21') ,('GNR','4','BROCHURE','FK213 R11/21') ,('GNR','7','RET_ENV','E2243 R2018') ,('GNR','8','OUT_ENV','E6058') ,('GNS','1','PAPER','FN319') ,('GNS','2','LETTER','FM906') ,('GNS','3','LETTER','FM839-19 R11/21') ,('GNS','4','BROCHURE','FK140 R11/21') ,('GNS','5','BROCHURE','FK213(19) R7/22') ,('GNS','7','RET_ENV','E2243 R2018') ,('GNS','8','OUT_ENV','E6058') ,('GNX','1','PAPER','FK212') ,('GNX','2','LETTER','FK172') ,('GNX','3','BROCHURE','FK213(05) R7/22') ,('GNX','4','BROCHURE','FK140 R11/21') ,('GNX','7','RET_ENV','E2243 R2018') ,('GNX','8','OUT_ENV','E6058') ,('GNY','1','PAPER','FK212') ,('GNY','2','LETTER','FK172') ,('GNY','3','BROCHURE','FK140(25) R11/21') ,('GNY','4','BROCHURE','FK213 R11/21') ,('GNY','7','RET_ENV','E2243 R2018') ,('GNY','8','OUT_ENV','E6058') ,('GNZ','1','PAPER','FK212') ,('GNZ','2','LETTER','FK172') ,('GNZ','3','BROCHURE','FK140(36) R11/21') ,('GNZ','4','BROCHURE','FK213 R11/21') ,('GNZ','7','RET_ENV','E2243 R2018') ,('GNZ','8','OUT_ENV','E6058') ,('GOA','1','PAPER','FK212') ,('GOA','2','LETTER','FK172') ,('GOA','3','BROCHURE','FK140(45) R7/22') ,('GOA','4','BROCHURE','FK213(45) R7/22') ,('GOA','7','RET_ENV','E2243 R2018') ,('GOA','8','OUT_ENV','E6058') ,('GOB','1','PAPER','FK212') ,('GOB','2','LETTER','FK172') ,('GOB','3','BROCHURE','FK140(49) R11/21') ,('GOB','4','BROCHURE','FK213 R11/21') ,('GOB','7','RET_ENV','E2243 R2018') ,('GOB','8','OUT_ENV','E6058') ,('GOC','1','PAPER','FK212') ,('GOC','2','LETTER','FK172') ,('GOC','3','BROCHURE','FK140 R11/21') ,('GOC','4','BROCHURE','FK213(42) R11/21') ,('GOC','5','INSERT','FN843(42) R11/21') ,('GOC','7','RET_ENV','E2243 R2018') ,('GOC','8','OUT_ENV','E6536-TX') ,('GOE','1','PAPER','FN319') ,('GOE','2','LETTER','FK172') ,('GOE','3','BROCHURE','FK140 R11/21') ,('GOE','4','BROCHURE','FK213 R11/21') ,('GOE','7','RET_ENV','E2243 R2018') ,('GOE','8','OUT_ENV','E6058') ,('GOF','1','PAPER','FK212') ,('GOF','2','LETTER','FM906') ,('GOF','3','LETTER','FN782-11 R11/21') ,('GOF','4','BROCHURE','FK140 R11/21') ,('GOF','5','BROCHURE','FK213(11) R7/22') ,('GOF','7','RET_ENV','E2243 R2018') ,('GOF','8','OUT_ENV','E6058') ,('GOO','1','PAPER','FK212') ,('GOO','2','LETTER','FM906') ,('GOO','3','LETTER','FO586-24 R11/21') ,('GOO','4','BROCHURE','FK140 R11/21') ,('GOO','5','BROCHURE','FK213(24) R7/22') ,('GOO','7','RET_ENV','E2243 R2018') ,('GOO','8','OUT_ENV','E6058') ,('GOQ','1','PAPER','FK212') ,('GOQ','2','LETTER','FR639-33') ,('GOQ','3','LETTER','FM906') ,('GOQ','4','BROCHURE','FK140 R11/21') ,('GOQ','5','BROCHURE','FK213(33) R7/22') ,('GOQ','7','RET_ENV','E2243 R2018') ,('GOQ','8','OUT_ENV','E6058') ,('GOS','1','PAPER','FK212') ,('GOS','2','LETTER','FK172') ,('GOS','3','BROCHURE','FS397(48)') ,('GOS','4','BROCHURE','FS398(48)') ,('GOS','7','RET_ENV','E2243 R2018') ,('GOS','8','OUT_ENV','E6058')

GO

CodePudding user response：

I went with a different approach, and this needs logic tweaking (I'm assuming you'd know enough about the specific use cases to do this) but this gets pretty close. You'll need to add exception logic to the CASE's but I think this can do what you want with a little extra tweaking.

DROP TABLE IF EXISTS #CompTable

CREATE TABLE #CompTable (    [PC_Pkg_Id] [varchar](3) NOT NULL,  [ColNum] [int] NOT NULL,    [PC_CompType] [varchar](20) NULL,   [PC_CompName] [varchar](20) NULL, ) ON [PRIMARY]

INSERT INTO #CompTable
           (
            [PC_Pkg_Id]
           ,[ColNum]
           ,[PC_CompType]
           ,[PC_CompName]
           )
     VALUES  ('GNL','1','PAPER','FK212') 
     ,('GNL','2','LETTER','FK172') 
     ,('GNL','3','BROCHURE','FK140 R11/21') 
     ,('GNL','4','BROCHURE','FK213 R11/21') 
     ,('GNL','7','RET_ENV','E2243 R2018') 
     ,('GNL','8','OUT_ENV','E6058') 
     ,('GNM','1','PAPER','FK212') 
     ,('GNM','2','LETTER','FK172') 
     ,('GNM','3','BROCHURE','FK140(50) R11/21') 
     ,('GNM','4','BROCHURE','FK213 R11/21') 
     ,('GNM','7','RET_ENV','E2243 R2018') 
     ,('GNM','8','OUT_ENV','E6058') 
     ,('GNP','1','PAPER','FK212') ,('GNP','2','LETTER','FK172') ,('GNP','3','BROCHURE','FK140 R11/21') ,('GNP','4','BROCHURE','FK213(28) R7/22') ,('GNP','7','RET_ENV','E2243 R2018') ,('GNP','8','OUT_ENV','E6058') ,('GNQ','1','PAPER','FK212') ,('GNQ','2','LETTER','FK172') ,('GNQ','3','BROCHURE','FK213(09) R7/22') ,('GNQ','4','BROCHURE','FK140(09) R11/21') ,('GNQ','7','RET_ENV','E2243 R2018') ,('GNQ','8','OUT_ENV','E6058') ,('GNR','1','PAPER','FK212') ,('GNR','2','LETTER','FK172') ,('GNR','3','BROCHURE','FK140(15) R11/21') ,('GNR','4','BROCHURE','FK213 R11/21') ,('GNR','7','RET_ENV','E2243 R2018') ,('GNR','8','OUT_ENV','E6058') ,('GNS','1','PAPER','FN319') ,('GNS','2','LETTER','FM906') ,('GNS','3','LETTER','FM839-19 R11/21') ,('GNS','4','BROCHURE','FK140 R11/21') ,('GNS','5','BROCHURE','FK213(19) R7/22') ,('GNS','7','RET_ENV','E2243 R2018') ,('GNS','8','OUT_ENV','E6058') ,('GNX','1','PAPER','FK212') ,('GNX','2','LETTER','FK172') ,('GNX','3','BROCHURE','FK213(05) R7/22') ,('GNX','4','BROCHURE','FK140 R11/21') ,('GNX','7','RET_ENV','E2243 R2018') ,('GNX','8','OUT_ENV','E6058') ,('GNY','1','PAPER','FK212') ,('GNY','2','LETTER','FK172') ,('GNY','3','BROCHURE','FK140(25) R11/21') ,('GNY','4','BROCHURE','FK213 R11/21') ,('GNY','7','RET_ENV','E2243 R2018') ,('GNY','8','OUT_ENV','E6058') ,('GNZ','1','PAPER','FK212') ,('GNZ','2','LETTER','FK172') ,('GNZ','3','BROCHURE','FK140(36) R11/21') ,('GNZ','4','BROCHURE','FK213 R11/21') ,('GNZ','7','RET_ENV','E2243 R2018') ,('GNZ','8','OUT_ENV','E6058') ,('GOA','1','PAPER','FK212') ,('GOA','2','LETTER','FK172') ,('GOA','3','BROCHURE','FK140(45) R7/22') ,('GOA','4','BROCHURE','FK213(45) R7/22') ,('GOA','7','RET_ENV','E2243 R2018') ,('GOA','8','OUT_ENV','E6058') ,('GOB','1','PAPER','FK212') ,('GOB','2','LETTER','FK172') ,('GOB','3','BROCHURE','FK140(49) R11/21') ,('GOB','4','BROCHURE','FK213 R11/21') ,('GOB','7','RET_ENV','E2243 R2018') ,('GOB','8','OUT_ENV','E6058') ,('GOC','1','PAPER','FK212') ,('GOC','2','LETTER','FK172') ,('GOC','3','BROCHURE','FK140 R11/21') ,('GOC','4','BROCHURE','FK213(42) R11/21') ,('GOC','5','INSERT','FN843(42) R11/21') ,('GOC','7','RET_ENV','E2243 R2018') ,('GOC','8','OUT_ENV','E6536-TX') ,('GOE','1','PAPER','FN319') ,('GOE','2','LETTER','FK172') ,('GOE','3','BROCHURE','FK140 R11/21') ,('GOE','4','BROCHURE','FK213 R11/21') ,('GOE','7','RET_ENV','E2243 R2018') ,('GOE','8','OUT_ENV','E6058') ,('GOF','1','PAPER','FK212') ,('GOF','2','LETTER','FM906') ,('GOF','3','LETTER','FN782-11 R11/21') ,('GOF','4','BROCHURE','FK140 R11/21') ,('GOF','5','BROCHURE','FK213(11) R7/22') ,('GOF','7','RET_ENV','E2243 R2018') ,('GOF','8','OUT_ENV','E6058') ,('GOO','1','PAPER','FK212') ,('GOO','2','LETTER','FM906') ,('GOO','3','LETTER','FO586-24 R11/21') ,('GOO','4','BROCHURE','FK140 R11/21') ,('GOO','5','BROCHURE','FK213(24) R7/22') ,('GOO','7','RET_ENV','E2243 R2018') ,('GOO','8','OUT_ENV','E6058') ,('GOQ','1','PAPER','FK212') ,('GOQ','2','LETTER','FR639-33') ,('GOQ','3','LETTER','FM906') ,('GOQ','4','BROCHURE','FK140 R11/21') ,('GOQ','5','BROCHURE','FK213(33) R7/22') ,('GOQ','7','RET_ENV','E2243 R2018') ,('GOQ','8','OUT_ENV','E6058') ,('GOS','1','PAPER','FK212') ,('GOS','2','LETTER','FK172') ,('GOS','3','BROCHURE','FS397(48)') ,('GOS','4','BROCHURE','FS398(48)') ,('GOS','7','RET_ENV','E2243 R2018') ,('GOS','8','OUT_ENV','E6058')

GO

SELECT PC_Pkg_Id
    , PAPER=MAX(PAPER)
    , LETTER=MAX(LETTER)
    , BROCHURE=MAX(BROCHURE)
    , BROCHURE=MAX(BROCHURE1)
    , [INSERT]=MAX([INSERT])
    , [INSERT]=MAX(INSERT1)
    , RET_ENV=MAX(RET_ENV)
    , OUT_ENV=MAX(OUT_ENV)
FROM (
    select PC_Pkg_Id
        , [PAPER] = case when ISNULL(ColNum, 1) = 1  then PC_CompName end 
        , [LETTER]= case when ISNULL(ColNum, 2) = 2  then PC_CompName end 
        , [BROCHURE] = case when ISNULL(ColNum, 3) = 3 then PC_CompName end 
        , [BROCHURE1] = case when ISNULL(ColNum, 4) = 4  then PC_CompName end 
        , [INSERT] = case when ISNULL(ColNum, 5) = 5  then PC_CompName end 
        , [INSERT1] = case when ISNULL(ColNum, 6) = 6  then PC_CompName end 
        , [RET_ENV] = case when ISNULL(ColNum, 7) = 7 then PC_CompName end 
        , [OUT_ENV] = case when ISNULL(ColNum, 8) = 8 then PC_CompName end 
    from #CompTable
    ) A
GROUP BY PC_Pkg_Id

CodePudding user response：

You could generate sql code and execute it eg

declare @sql nvarchar(max);

set @sql = 
stuff(
(select concat(' max(case when t.colnum = ', s.colnum ,' then PC_CompName end) as ' ,pc_comptype ,',')
from 
(select distinct colnum,case when pc_comptype is null then '[Insert]' else pc_comptype end pc_comptype from t) s
for xml path(''))
,1,1,'')

set @sql = left(@sql,len(@sql) -1)
set @sql = (select concat('select PC_Pkg_Id, ', @sql ,' from t group by PC_Pkg_Id'))

--select @sql

EXEC sp_executesql @sql