SQL convert Rows to columns
-
I have seen several posts on this many of which suggest Pivot. I am trying to mimic a MS Access report that used VBA to format the columns. I have been working on this for awhile and I am still no closer to solving it, here is the Problem. Some of my attempts have involved nested looping which seems to work but I would really like to see a example of Pivot if someone can help with that.
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 will 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. Column 2 and three are always Paper and Letter. RET_ENV always goes in Column 8, RET_ENV always goes in COLUMN 9. The number of brochures and Inserts can vary. Some packages may have 4 Brochures and no inserts, some may will have all inserts and no brochures. if there are less than 4 Brochure\Inserts. Missing Columns Should have a header of "INSERT".
In my Nested while loop solution, I read the records once to build a header row then looped through each to populate the components in each column. Mind you I haven't got this fully functional but with my level of experience it seems like it will work.
Any guidance is greatly appreciated. Thanks
Input Record Layout--
PC_Pkg_Id Char(3) The Group field
ColNum Int The column across data needs to appear in
PC_CompType Char(20) Column Header
PC_CompName Char(20) Column value
output required--
Sample Data ---
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- This topic was modified 2 years, 4 months ago by wburke 85918.
- This topic was modified 2 years, 4 months ago by wburke 85918.
-
The following method is likely to be faster and easier to read than PIVOT:
(Note that column names must be unique)
SELECT PKGId = ct.PC_Pkg_Id
,Paper = MAX( CASE ct.ColNum
WHEN 1 THEN
ct.PC_CompName
END
)
,Letter = MAX( CASE ct.ColNum
WHEN 2 THEN
ct.PC_CompName
END
)
,Brochure1 = MAX( CASE ct.ColNum
WHEN 3 THEN
ct.PC_CompName
END
)
,Brochure2 = MAX( CASE ct.ColNum
WHEN 4 THEN
ct.PC_CompName
END
)
,Insert1 = MAX( CASE ct.ColNum
WHEN 5 THEN
ct.PC_CompName
END
)
,Insert2 = MAX( CASE ct.ColNum
WHEN 6 THEN
ct.PC_CompName
END
)
,Ret_Env = MAX( CASE ct.ColNum
WHEN 7 THEN
ct.PC_CompName
END
)
,Out_Env = MAX( CASE ct.ColNum
WHEN 8 THEN
ct.PC_CompName
END
)
FROM dbo.CompTable ct
GROUP BY ct.PC_Pkg_Id
ORDER BY ct.PC_Pkg_Id;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Thanks for your reply Phil, Someone suggested PIVOT would do this, so I was trying to figure out how to do that. While this would be the answer if the columns were always the same. The column names change, that's why I included PC_CompType which contains the column names. My original try was using nested While's solution, I read the first set of package ID's building the first row which contains the Column names. Actual column headers were Colunm1-Colunm9.
- wburke 85918 wrote:
Thanks for your reply Phil, Someone suggested PIVOT would do this, so I was trying to figure out how to do that. While this would be the answer if the columns were always the same. The column names change, that's why I included PC_CompType which contains the column names. My original try was using nested While's solution, I read the first set of package ID's building the first row which contains the Column names. Actual column headers were Colunm1-Colunm9.
Phil used an ancient but incredibly effect method known as a CrossTab. It used to be documented in the MS "Books Online" help system that came with SQL Server. It's still incredibly effective and usually faster than using a PIVOT. It takes a bit more code but it was usually faster.
If you want to make one like Phil did except be dynamic, please see the following article. The article concentrates on the SUM(s) of numeric values but it works just fine with MAX(), like Phil's code does. Here's the link...
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Phil, its not that your solution didn't have merit. I was able to jazz it up by using a UNION, got close but no cigar. Still the issue with the Column names. I guess "Brochure" is less than "Insert" so "Insert" is printed instead of Brochure.
DECLARE @Pkg_Id AS Char(3) = (SELECT TOP 1 PC_Pkg_Id FROM [dbo].[CompTable])
SELECT * FROM
(
SELECT RowType = 1, PKGId = 'Pkg Id'
,Column1 = MAX(CASE ct.ColNum WHEN 1 THEN ct.PC_CompType Else 'Insert' END )
,Column2 = MAX(CASE ct.ColNum WHEN 2 THEN ct.PC_CompType Else 'Insert' END )
,Column3 = MAX(CASE ct.ColNum WHEN 3 THEN ct.PC_CompType Else 'Insert' END )
,Column4 = MAX(CASE ct.ColNum WHEN 4 THEN ct.PC_Comptype Else 'Insert' END )
,Column5 = MAX(CASE ct.ColNum WHEN 5 THEN ct.PC_Comptype Else 'Insert' END )
,Column6 = MAX(CASE ct.ColNum WHEN 6 THEN ct.PC_Comptype Else 'Insert' END )
,Column7 = MAX(CASE ct.ColNum WHEN 7 THEN ct.PC_Comptype Else 'Insert' END )
,Column8 = MAX(CASE ct.ColNum WHEN 8 THEN ct.PC_Comptype Else 'Insert' END )
FROM dbo.CompTable ct
WHERE PC_Pkg_Id = @Pkg_Id
GROUP BY ct.PC_Pkg_Id
) a
UNION
(SELECT RowType = 2, PKGId = ct.PC_Pkg_Id
,Column1 = MAX(CASE ct.ColNum WHEN 1 THEN ct.PC_CompName END )
,Column2 = MAX(CASE ct.ColNum WHEN 2 THEN ct.PC_CompName END )
,Column3 = MAX(CASE ct.ColNum WHEN 3 THEN ct.PC_CompName END )
,Column4 = MAX(CASE ct.ColNum WHEN 4 THEN ct.PC_CompName END )
,Column5 = MAX(CASE ct.ColNum WHEN 5 THEN ct.PC_CompName END )
,Column6 = MAX(CASE ct.ColNum WHEN 6 THEN ct.PC_CompName END )
,Column7 = MAX(CASE ct.ColNum WHEN 7 THEN ct.PC_CompName END )
,Column8 = MAX(CASE ct.ColNum WHEN 8 THEN ct.PC_CompName END )
FROM dbo.CompTable ct
GROUP BY ct.PC_Pkg_Id
)
ORDER BY RowType -
Wow, that's a very good article Jeff, comparing Pivot to Cross Tabs. I was noticing all your Pivots always have aggregate functions. What I am doing seems to involve grouping more than math. So is there a way to use pivot that does not involve a aggregate. I know that's probably a dumb question. One of our DBA's suggested I try Pivot like a year ago. At this point I feel like its been a wild goose chase, for this project at least. I have to know can it be used for what I am trying to do?
- wburke 85918 wrote:
Thanks for your reply Phil, Someone suggested PIVOT would do this, so I was trying to figure out how to do that. While this would be the answer if the columns were always the same. The column names change, that's why I included PC_CompType which contains the column names. My original try was using nested While's solution, I read the first set of package ID's building the first row which contains the Column names. Actual column headers were Colunm1-Colunm9.
If I run the following query on your test data, there are two results – BROCHURE and LETTER. So how would you determine the name for ColNum 3? ColNum 5 has a similar outcome.
SELECT DISTINCT ct.PC_CompType
FROM dbo.CompTable ct
WHERE ct.ColNum = 3The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I know its kind of forced in that, we use whatever the PC_CompType's are on first Package, for all the packages in the set. I think maybe we should just use the generic term Insert for anything that is not a Paper, Ret_Env or Out_Env, But this is the way they have printed this report for the last 13 years so that's what they are used to seeing.
If you have not figured it out this machine that stuffs envelops. It has eight slots for components. Imagine your power bill, A couple of things you always get is a Bill (Paper), A return envelope, and its all placed in a outer envelope. Now the power company wants you to know how great a job they are doing so they usually include a Letter or two. Then they have figured out they can also sell people high priced thermostats and other energy saving gadgets so they send a Brochure or two as well. Order matters first Bill, Next to last last return env, Last outer env.
-
This was removed by the editor as SPAM
-
Here is another attempt, this time with dynamic column names. As mentioned before, column names must be unique, so I added some suffixes to ensure this.
DECLARE @C0 VARCHAR(50)
,@C1 VARCHAR(50)
,@C2 VARCHAR(50)
,@C3 VARCHAR(50)
,@C4 VARCHAR(50)
,@C5 VARCHAR(50)
,@C6 VARCHAR(50)
,@C7 VARCHAR(50)
,@C8 VARCHAR(50);
SELECT @C0 = 'PkgId'
,@C1 = 'PAPER'
,@C2 = 'LETTER'
,@C3 = ISNULL(MAX( CASE ct.ColNum
WHEN 3 THEN
CONCAT(ct.PC_CompType, '1')
END
)
,'Insert1'
)
,@C4 = ISNULL(MAX( CASE ct.ColNum
WHEN 4 THEN
CONCAT(ct.PC_CompType, '2')
END
)
,'Insert2'
)
,@C5 = ISNULL(MAX( CASE ct.ColNum
WHEN 5 THEN
CONCAT(ct.PC_CompType, '3')
END
)
,'Insert3'
)
,@C6 = ISNULL(MAX( CASE ct.ColNum
WHEN 6 THEN
CONCAT(ct.PC_CompType, '4')
END
)
,'Insert4'
)
,@C7 = 'RET_ENV'
,@C8 = 'OUT_ENV'
FROM dbo.CompTable ct;
DECLARE @SQL NVARCHAR(4000)
= CONCAT(
'SELECT '
,@C0
,' = ct.PC_Pkg_Id
,'
,@C1
,' = MAX( CASE ct.ColNum
WHEN 1 THEN
ct.PC_CompName
END
)
,'
,@C2
,' = MAX( CASE ct.ColNum
WHEN 2 THEN
ct.PC_CompName
END
)
,'
,@C3
,' = MAX( CASE ct.ColNum
WHEN 3 THEN
ct.PC_CompName
END
)
,'
,@C4
,' = MAX( CASE ct.ColNum
WHEN 4 THEN
ct.PC_CompName
END
)
,'
,@C5
,' = MAX( CASE ct.ColNum
WHEN 5 THEN
ct.PC_CompName
END
)
,'
,@C6
,' = MAX( CASE ct.ColNum
WHEN 6 THEN
ct.PC_CompName
END
)
,'
,@C7
,' = MAX( CASE ct.ColNum
WHEN 7 THEN
ct.PC_CompName
END
)
,'
,@C8
,' = MAX( CASE ct.ColNum
WHEN 8 THEN
ct.PC_CompName
END
)
FROM dbo.CompTable ct
GROUP BY ct.PC_Pkg_Id
ORDER BY ct.PC_Pkg_Id'
);
EXEC sys.sp_executesql @stmt = @SQL;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Thank you all for the very useful, and most importantly, clear information.
-
Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for creating it. Looking forward for solution.
- thomas8927eva wrote:
Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for creating it. Looking forward for solution.
I provided the solution. If it is not the solution, please tell me why, thank you.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin wrote:thomas8927eva wrote:
T
I provided the solution. If it is not the solution, please tell me why, thank you.
Phil, You have gotten me closer than I have been. Your solution was elegant and efficient. The only problem is it defaults the Colum names. I need it to use whatever is there, unless it is missing then it would use INSERT. I am using your original code tweaking it to get what I need. I see what you did in you last response so I will try to incorporate that into the solution. Thanks again for all your help.
Here is the final solution
DECLARE @Pkg_Id AS Char(3) = (SELECT TOP 1 PC_Pkg_Id FROM [dbo].[CompTable])
SELECT * FROM
(
SELECT RowType = 1, PKGId = 'Pkg Id'
,Column1 = ISNULL(MAX(CASE ct.ColNum WHEN 1 THEN ct.PC_CompType END ), 'INSERT')
,Column2 = ISNULL(MAX(CASE ct.ColNum WHEN 2 THEN ct.PC_CompType END ), 'INSERT')
,Column3 = ISNULL(MAX(CASE ct.ColNum WHEN 3 THEN ct.PC_CompType END ), 'INSERT')
,Column4 = ISNULL(MAX(CASE ct.ColNum WHEN 4 THEN ct.PC_Comptype END ), 'INSERT')
,Column5 = ISNULL(MAX(CASE ct.ColNum WHEN 5 THEN ct.PC_Comptype END ), 'INSERT')
,Column6 = ISNULL(MAX(CASE ct.ColNum WHEN 6 THEN ct.PC_Comptype END ), 'INSERT')
,Column7 = ISNULL(MAX(CASE ct.ColNum WHEN 7 THEN ct.PC_Comptype END ), 'INSERT')
,Column8 = ISNULL(MAX(CASE ct.ColNum WHEN 8 THEN ct.PC_Comptype END ), 'INSERT')
FROM dbo.CompTable ct
WHERE PC_Pkg_Id = @Pkg_Id
GROUP BY ct.PC_Pkg_Id
) a
UNION
(SELECT RowType = 2, PKGId = ct.PC_Pkg_Id
,Column1 = MAX(CASE ct.ColNum WHEN 1 THEN ct.PC_CompName Else ' ' END )
,Column2 = MAX(CASE ct.ColNum WHEN 2 THEN ct.PC_CompName Else ' ' END )
,Column3 = MAX(CASE ct.ColNum WHEN 3 THEN ct.PC_CompName Else ' ' END )
,Column4 = MAX(CASE ct.ColNum WHEN 4 THEN ct.PC_CompName Else ' ' END )
,Column5 = MAX(CASE ct.ColNum WHEN 5 THEN ct.PC_CompName Else ' ' END )
,Column6 = MAX(CASE ct.ColNum WHEN 6 THEN ct.PC_CompName Else ' ' END )
,Column7 = MAX(CASE ct.ColNum WHEN 7 THEN ct.PC_CompName Else ' ' END )
,Column8 = MAX(CASE ct.ColNum WHEN 8 THEN ct.PC_CompName Else ' ' END )
FROM dbo.CompTable ct
GROUP BY ct.PC_Pkg_Id
)
ORDER BY RowType,PKGId -
It seems my insistence on unique column names was misguided. This is not required.
I am using a temp table while working on your code. Please try executing the following and let me know what you think. The way in which the column names are derived can be tweaked.
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,
);
INSERT #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');
DECLARE @C0 VARCHAR(50)
,@C1 VARCHAR(50)
,@C2 VARCHAR(50)
,@C3 VARCHAR(50)
,@C4 VARCHAR(50)
,@C5 VARCHAR(50)
,@C6 VARCHAR(50)
,@C7 VARCHAR(50)
,@C8 VARCHAR(50);
SELECT @C0 = 'PkgId'
,@C1 = 'PAPER'
,@C2 = 'LETTER'
,@C3 = ISNULL(MAX( CASE ct.ColNum
WHEN 3 THEN
QUOTENAME(ct.PC_CompType)
END
)
,'[INSERT]'
)
,@C4 = ISNULL(MAX( CASE ct.ColNum
WHEN 4 THEN
QUOTENAME(ct.PC_CompType)
END
)
,'[INSERT]'
)
,@C5 = ISNULL(MAX( CASE ct.ColNum
WHEN 5 THEN
QUOTENAME(ct.PC_CompType)
END
)
,'[INSERT]'
)
,@C6 = ISNULL(MAX( CASE ct.ColNum
WHEN 6 THEN
QUOTENAME(ct.PC_CompType)
END
)
,'[INSERT]'
)
,@C7 = 'RET_ENV'
,@C8 = 'OUT_ENV'
FROM #CompTable ct;
DECLARE @SQL NVARCHAR(4000)
= CONCAT(
'SELECT '
,@C0
,' = ct.PC_Pkg_Id
,'
,@C1
,' = MAX( CASE ct.ColNum
WHEN 1 THEN
ct.PC_CompName
END
)
,'
,@C2
,' = MAX( CASE ct.ColNum
WHEN 2 THEN
ct.PC_CompName
END
)
,'
,@C3
,' = MAX( CASE ct.ColNum
WHEN 3 THEN
ct.PC_CompName
END
)
,'
,@C4
,' = MAX( CASE ct.ColNum
WHEN 4 THEN
ct.PC_CompName
END
)
,'
,@C5
,' = MAX( CASE ct.ColNum
WHEN 5 THEN
ct.PC_CompName
END
)
,'
,@C6
,' = MAX( CASE ct.ColNum
WHEN 6 THEN
ct.PC_CompName
END
)
,'
,@C7
,' = MAX( CASE ct.ColNum
WHEN 7 THEN
ct.PC_CompName
END
)
,'
,@C8
,' = MAX( CASE ct.ColNum
WHEN 8 THEN
ct.PC_CompName
END
)
FROM #CompTable ct
GROUP BY ct.PC_Pkg_Id
ORDER BY ct.PC_Pkg_Id'
);
--PRINT @SQL
EXEC sys.sp_executesql @stmt = @SQL;The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply