November 18, 2013 at 6:03 pm
Looked all over, found rows to columns can't find this.
Desired Result (notice A distinct) two fields, one with id, other with data and comma between
IDID DATA
A P1, P2, P3, P4
B P2
C P3, P4
D P3, P5
CREATE TABLE #abc
(IDID varchar(1), Data varchar(2))
INSERT INTO #Unpivot
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P3' UNION ALL
SELECT 'A', 'P4' UNION ALL
SELECT 'B', 'P2' UNION ALL
SELECT 'C', 'P3' UNION ALL
SELECT 'C', 'P4' UNION ALL
SELECT 'D', 'P3' UNION ALL
SELECT 'D', 'P5'
November 18, 2013 at 10:14 pm
Probably something like this.
WITH SampleData (ID, Data) AS
(
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P3' UNION ALL
SELECT 'A', 'P4' UNION ALL
SELECT 'B', 'P2' UNION ALL
SELECT 'C', 'P3' UNION ALL
SELECT 'C', 'P4' UNION ALL
SELECT 'D', 'P3' UNION ALL
SELECT 'D', 'P5'
)
SELECT ID
,D1=MAX(CASE rn WHEN 1 THEN Data END)
,D2=MAX(CASE rn WHEN 2 THEN Data END)
,D3=MAX(CASE rn WHEN 3 THEN Data END)
,D4=MAX(CASE rn WHEN 4 THEN Data END)
,D5=MAX(CASE rn WHEN 5 THEN Data END)
FROM
(
SELECT ID, Data, rn=ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Data)
FROM (
SELECT DISTINCT ID, Data
FROM SampleData
) a
) a
GROUP BY ID;
If you need to define your columns dynamically, read this: Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 18, 2013 at 10:29 pm
try this
Select distinct IDID, Data= REPLACE((Select distinct t1.data as [data()]
From #Unpivot t1 Where t1.idid = t2.idid Order by t1.data FOR XML PATH ('') ), ' ', ',')
November 19, 2013 at 12:16 am
This should work for you
SELECT
ID
,LEFT(dd.Data_Values, LEN(dd.Data_Values) - 1) Data
FROM
#Unpivot up
CROSS APPLY ( SELECT
CAST(DATA AS VARCHAR) + ','
FROM
#Unpivot up1
WHERE
up1.ID = up.ID
FOR
XML PATH('') ) dd ( Data_Values )
GROUP BY
ID
,dd.Data_Values
November 19, 2013 at 8:17 am
I'm just modifying Dwain's query to reduce the code by using dense_rank.
WITH SampleData (ID, Data) AS
(
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P3' UNION ALL
SELECT 'A', 'P4' UNION ALL
SELECT 'B', 'P2' UNION ALL
SELECT 'C', 'P3' UNION ALL
SELECT 'C', 'P4' UNION ALL
SELECT 'D', 'P3' UNION ALL
SELECT 'D', 'P5'
)
SELECT ID
,D1=MAX(CASE rn WHEN 1 THEN Data END)
,D2=MAX(CASE rn WHEN 2 THEN Data END)
,D3=MAX(CASE rn WHEN 3 THEN Data END)
,D4=MAX(CASE rn WHEN 4 THEN Data END)
,D5=MAX(CASE rn WHEN 5 THEN Data END)
FROM
(
SELECT ID, Data, rn=DENSE_RANK() OVER (PARTITION BY ID ORDER BY Data)
FROM SampleData a
) a
GROUP BY ID;
November 19, 2013 at 8:24 am
And now correcting previous posted queries as they had syntax errors.
Select distinct IDID,
Data= REPLACE((Select distinct t1.data as [data()]
From #Unpivot t1
Where t1.IDID = t2.IDID
Order by t1.data FOR XML PATH ('') ), ' ', ',')
FROM #Unpivot t2
SELECT IDID
,LEFT(dd.Data_Values, LEN(dd.Data_Values) - 1) Data
FROM #Unpivot up
CROSS APPLY ( SELECT DISTINCT CAST(DATA AS VARCHAR) + ','
FROM #Unpivot up1
WHERE up1.IDID = up.IDID
FOR XML PATH('') ) dd ( Data_Values )
GROUP BY IDID ,dd.Data_Values
November 19, 2013 at 5:21 pm
Luis Cazares (11/19/2013)
I'm just modifying Dwain's query to reduce the code by using dense_rank.
WITH SampleData (ID, Data) AS(
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P1' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P2' UNION ALL
SELECT 'A', 'P3' UNION ALL
SELECT 'A', 'P4' UNION ALL
SELECT 'B', 'P2' UNION ALL
SELECT 'C', 'P3' UNION ALL
SELECT 'C', 'P4' UNION ALL
SELECT 'D', 'P3' UNION ALL
SELECT 'D', 'P5'
)
SELECT ID
,D1=MAX(CASE rn WHEN 1 THEN Data END)
,D2=MAX(CASE rn WHEN 2 THEN Data END)
,D3=MAX(CASE rn WHEN 3 THEN Data END)
,D4=MAX(CASE rn WHEN 4 THEN Data END)
,D5=MAX(CASE rn WHEN 5 THEN Data END)
FROM
(
SELECT ID, Data, rn=DENSE_RANK() OVER (PARTITION BY ID ORDER BY Data)
FROM SampleData a
) a
GROUP BY ID;
+1 - DENSE_RANK didn't even come to mind for some reason.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply