March 14, 2012 at 11:06 am
Hi all,
I have a table that looks like this.
CREATE TABLE #tmp1 (PartID VARCHAR(8), CodeA VARCHAR(20), CodeB VARCHAR(20), CodeC VARCHAR(20) )
INSERT INTO #tmp1 VALUES ('PHI620', NULL, NULL, NULL)
INSERT INTO #tmp1 VALUES ('PHI620', NULL, NULL, '26548')
INSERT INTO #tmp1 VALUES ('PHI620', NULL, NULL, '99548')
INSERT INTO #tmp1 VALUES ('PHI620', NULL, '55555', NULL)
INSERT INTO #tmp1 VALUES ('PHI620', '987-d52', NULL, NULL)
INSERT INTO #tmp1 VALUES ('PHI620', 'xc8-95v', NULL, NULL)
INSERT INTO #tmp1 VALUES ('PHI620', '552-95w', NULL, NULL)
INSERT INTO #tmp1 VALUES ('PHI620', '302-7r7', NULL, NULL)
INSERT INTO #tmp1 VALUES ('BSC95P', NULL, NULL, NULL)
INSERT INTO #tmp1 VALUES ('BSC95P', NULL, NULL, '64e4m')
INSERT INTO #tmp1 VALUES ('BSC95P', NULL, NULL, '45g41')
INSERT INTO #tmp1 VALUES ('BSC95P', NULL, '556g5', NULL)
INSERT INTO #tmp1 VALUES ('BSC95P', '987-d5m', NULL, NULL)
INSERT INTO #tmp1 VALUES ('BSC95P', 'xc8-65v', NULL, NULL)
INSERT INTO #tmp1 VALUES ('BSC95P', 'k5j-95w', NULL, NULL)
INSERT INTO #tmp1 VALUES ('BSC95P', 'g52-735', NULL, NULL)
and would like to get something like this
SELECT
'PHI620' AS [PartID]
,'987-d52' AS [CodeA-1]
,'xc8-95v' AS [CodeA-2]
,'552-95w' AS [CodeA-3]
,'302-7r7' AS [CodeA-4]
,'55555' AS [CodeB-1]
,'26548' AS [CodeC-1]
,'99548' AS [CodeC-2]
UNION
SELECT
'BSC95P' AS [PartID]
,'987-d5m' AS [CodeA-1]
,'xc8-65v' AS [CodeA-2]
,'k5j-95w' AS [CodeA-3]
,'g52-735' AS [CodeA-4]
,'556g5' AS [CodeB-1]
,'45g41' AS [CodeC-1]
,'64e4m' AS [CodeC-2]
Up to 10 CodeA / CodeB / CodeC may belong to each PartID and the column numbers / names should not change (this is a part of an SSRS report), eg CodeA-1 ... CodeA-10, CodeB-1 ... CodeB-10, etc
So how do I pivot this??
Thank you.
__________________________
Allzu viel ist ungesund...
March 14, 2012 at 11:24 am
This should do the trick:
SELECT *
FROM (
SELECT PartId,
value,
attribute = attribute + CAST( ROW_NUMBER() OVER(PARTITION BY PartId, attribute ORDER BY PartId, attribute) AS varchar(10))
FROM (
SELECT PartID,
value = COALESCE(CodeA, CodeB, CodeC),
attribute = CASE WHEN CodeA IS NOT NULL THEN 'CodeA' ELSE CASE WHEN CodeB IS NOT NULL THEN 'CodeB' ELSE 'CodeC' END END
FROM #tmp1
) AS src
) AS src2
PIVOT (MIN(value) FOR attribute in (
[CodeA-1],[CodeA-2],[CodeA-3],[CodeA-4],[CodeA-5],[CodeA-6],[CodeA-7],[CodeA-8],[CodeA-9],[CodeA-10],
[CodeB-1],[CodeB-2],[CodeB-3],[CodeB-4],[CodeB-5],[CodeB-6],[CodeB-7],[CodeB-8],[CodeB-9],[CodeB-10],
[CodeC-1],[CodeC-2],[CodeC-3],[CodeC-4],[CodeC-5],[CodeC-6],[CodeC-7],[CodeC-8],[CodeC-9],[CodeC-10])
) AS p;
-- Gianluca Sartori
March 14, 2012 at 11:35 am
Gianluca Sartori (3/14/2012)
This should do the trick:
SELECT *
FROM (
SELECT PartId,
value,
attribute = attribute + CAST( ROW_NUMBER() OVER(PARTITION BY PartId, attribute ORDER BY PartId, attribute) AS varchar(10))
FROM (
SELECT PartID,
value = COALESCE(CodeA, CodeB, CodeC),
attribute = CASE WHEN CodeA IS NOT NULL THEN 'CodeA' ELSE CASE WHEN CodeB IS NOT NULL THEN 'CodeB' ELSE 'CodeC' END END
FROM #tmp1
) AS src
) AS src2
PIVOT (MIN(value) FOR attribute in (
[CodeA-1],[CodeA-2],[CodeA-3],[CodeA-4],[CodeA-5],[CodeA-6],[CodeA-7],[CodeA-8],[CodeA-9],[CodeA-10],
[CodeB-1],[CodeB-2],[CodeB-3],[CodeB-4],[CodeB-5],[CodeB-6],[CodeB-7],[CodeB-8],[CodeB-9],[CodeB-10],
[CodeC-1],[CodeC-2],[CodeC-3],[CodeC-4],[CodeC-5],[CodeC-6],[CodeC-7],[CodeC-8],[CodeC-9],[CodeC-10])
) AS p;
One small bug here [CodeA-1],[CodeA-2],[ should be [CodeA1],[CodeA2],[.
Or,
attribute = attribute + CAST( ROW_NUMBER() OVER(PARTITION BY PartId, attribute ORDER BY PartId, attribute) AS varchar(10))
should be
attribute = attribute +'-'+ CAST( ROW_NUMBER() OVER(PARTITION BY PartId, attribute ORDER BY PartId, attribute) AS varchar(10))
Remove the hyphen from all the codes (this is because, in the sub-squery src, Gian is concatenating the row_nhumber directly to the Column names.
March 14, 2012 at 11:51 am
Ah! Thanks Mr. Coffee!
I changed the column names because the OP wanted them with the '-' inside, but I forgot to change the code.
This should do:
SELECT *
FROM (
SELECT PartId,
value,
attribute = attribute + '-' + CAST( ROW_NUMBER() OVER(PARTITION BY PartId, attribute ORDER BY PartId, attribute) AS varchar(10))
FROM (
SELECT PartID,
value = COALESCE(CodeA, CodeB, CodeC),
attribute = CASE WHEN CodeA IS NOT NULL THEN 'CodeA' ELSE CASE WHEN CodeB IS NOT NULL THEN 'CodeB' ELSE 'CodeC' END END
FROM #tmp1
) AS src
) AS src2
PIVOT (MIN(value) FOR attribute in (
[CodeA-1],[CodeA-2],[CodeA-3],[CodeA-4],[CodeA-5],[CodeA-6],[CodeA-7],[CodeA-8],[CodeA-9],[CodeA-10],
[CodeB-1],[CodeB-2],[CodeB-3],[CodeB-4],[CodeB-5],[CodeB-6],[CodeB-7],[CodeB-8],[CodeB-9],[CodeB-10],
[CodeC-1],[CodeC-2],[CodeC-3],[CodeC-4],[CodeC-5],[CodeC-6],[CodeC-7],[CodeC-8],[CodeC-9],[CodeC-10])
) AS p;
-- Gianluca Sartori
March 14, 2012 at 11:53 am
Gianluca Sartori (3/14/2012)
Ah! Thanks Mr. Coffee!
No problem, Gian!
March 14, 2012 at 12:03 pm
Thanks very much for your reply
Will check this thing out once I'm back in office tomorrow. My idea is just to hide the empty cols in SSRS rather than generating them dynamically in the stored procedure (and deal with them somehow in my report). Does that make sense?
__________________________
Allzu viel ist ungesund...
March 14, 2012 at 12:12 pm
Makes sense.
You would have to use dynamic SQL and perform a select distinct to make the column names dynamic in SQL.
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply