October 14, 2014 at 7:18 pm
I have 2 different types of data for the same key. My queries renders these results into 2 rows. I'd like them to be one row.
Example
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1
Boxer Maggie Brindle 4
Lab Jackson Yellow 2
Lab Sandie Black 3
The result I want
DOG Name_a Color_a Age_a Name_B Color_B Age_B
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3
October 14, 2014 at 10:18 pm
Quick cross-tab solution.
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(DOG,Name,Color,Age) AS
( SELECT * FROM (VALUES
('Boxer' ,'BO' ,'Fawn' ,1)
,('Boxer' ,'Maggie' ,'Brindle' ,4)
,('Lab' ,'Jackson' ,'Yellow' ,2)
,('Lab' ,'Sandie' ,'Black' ,3)
)AS X(DOG,Name,Color,Age)
)
,GROUPED_SET AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY SD.Dog
ORDER BY SD.Name
) AS SD_RID
,SD.DOG
,SD.Name
,SD.Color
,SD.Age
FROM SAMPLE_DATA SD
)
SELECT
GS.DOG
,MAX(CASE WHEN GS.SD_RID = 1 THEN GS.Name END) AS Name_1
,MAX(CASE WHEN GS.SD_RID = 1 THEN GS.Color END) AS Color_1
,MAX(CASE WHEN GS.SD_RID = 1 THEN GS.Age END) AS Age_1
,MAX(CASE WHEN GS.SD_RID = 2 THEN GS.Name END) AS Name_2
,MAX(CASE WHEN GS.SD_RID = 2 THEN GS.Color END) AS Color_2
,MAX(CASE WHEN GS.SD_RID = 2 THEN GS.Age END) AS Age_2
FROM GROUPED_SET GS
GROUP BY GS.DOG;
Results
DOG Name_1 Color_1 Age_1 Name_2 Color_2 Age_2
----- ------- ------- ----------- ------- ------- -----------
Boxer BO Fawn 1 Maggie Brindle 4
Lab Jackson Yellow 2 Sandie Black 3
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply