CODEWITHSUNDEEP
sqlsql-server
HoneyBadger
HoneyBadger

Reputation: 15150

Merging rows to columns

I have the following situation (heavily abstracted, please ignore bad design):

CREATE TABLE dbo.PersonTest (Id INT, name VARCHAR(255))

INSERT INTO dbo.PersonTest
        (Id, name )
VALUES  (1, 'Pete')
,       (1, 'Marie')
,       (2, 'Sam')
,       (2, 'Daisy')

I am looking for the following result:

Id  Name1   Name2
1   Marie   Pete
2   Daisy   Sam

So, for each Id, the rows should be merged.

Getting this result I used the following query:

WITH PersonRN AS
(
    SELECT  *
    ,       ROW_NUMBER() OVER(PARTITION BY Id ORDER BY name) RN
    FROM    dbo.PersonTest
)

SELECT      PT1.Id
,           PT1.name Name1
,           PT2.name Name2
FROM        PersonRN AS PT1
LEFT JOIN   PersonRN AS PT2 -- Left join in case there's only 1 name
        ON  PT2.Id = PT1.Id
        AND PT2.RN = 2
WHERE       PT1.RN = 1

Which works perfectly fine.

My question is: Is this the best way (best in terms of performance and resilience)? If, for example, one of these Id's has a third name, this third name is ignored by my query. I'm thinking the best way to deal with that would be dynamic SQL, which would be fine, but if it can be done without dynamic, I would prefer that.

Upvotes: 2

Views: 161

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Aside from dynamic PIVOT, you can do this using Dynamic Crosstab, which I prefer for readability.

SQL Fiddle

DECLARE @sql1 VARCHAR(1000) = '',
        @sql2 VARCHAR(1000) = '',
        @sql3 VARCHAR(1000) = ''
DECLARE @max INT

SELECT TOP 1 @max = COUNT(*) FROM PersonTest GROUP BY ID ORDER BY COUNT(*) DESC

SELECT @sql1 = 
'SELECT
    ID' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN RN =' + CONVERT(VARCHAR(5), RN) 
    + ' THEN name END) AS ' + QUOTENAME('Name' + CONVERT(VARCHAR(5), RN)) + CHAR(10)
FROM(
    SELECT TOP(@max)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RN
    FROM sys.columns
)t
ORDER BY RN

SELECT @sql3 =
'FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY name)
    FROM PersonTest
)t
GROUP BY ID
ORDER BY ID'

PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

Upvotes: 4

Related Questions