CODEWITHSUNDEEP
sqlsql-server-2008t-sqlgrouping
JBarnes
JBarnes

Reputation: 491

T-SQL Group Rows Into Columns

How can I group an (unknown) number of rows into a single row where the set columns determine the grouping?

For example, shift

Ref      Name            Link
==============================
1        John            L1
1        John            L2
1        John            L8
2        Steve           L1
2        Steve           L234

Into

Ref      Name            ...    ...    ...
==========================================
1        John            L1     L2     L8
2        Steve           L1     L234   NULL

Thanks for any help

Upvotes: 9

Views: 13694

Answers (2)

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

You might pivot the table using row_number() as a source of column names:

select *
from
(
  select ref, 
         name, 
         link,
         row_number() over (partition by ref, name order by link) rn
  from table1
) s
pivot (min (link) for rn in ([1], [2], [3], [4])) pvt

Simply extend the list of numbers if you have more rows.

Live test is @ Sql Fiddle.

Upvotes: 12

GarethD
GarethD

Reputation: 69819

If the number of different Links is unkown this needs to be done dynamically. I think this will work as required:

DECLARE @SQL NVARCHAR(MAX) = ''
SELECT  @SQL = @SQL + ',' + QUOTENAME(Rownumber)
FROM    (   SELECT  DISTINCT ROW_NUMBER() OVER(PARTITION BY Ref, Name ORDER BY Link) [RowNumber]
            FROM    yourTable
        ) d

SET @SQL = 'SELECT  *
            FROM    (   SELECT  Ref, 
                                name, 
                                Link, 
                                ROW_NUMBER() OVER(PARTITION BY Ref, Name ORDER BY Link) [RowNumber]
                        FROM    yourTable
                    ) data
                    PIVOT
                    (   MAX(Link)
                        FOR RowNumber IN (' + STUFF(@SQL, 1, 1, '') + ')
                    ) pvt'

EXECUTE SP_EXECUTESQL @SQL

It is a slight varation of the usual PIVOT function because normally link would be in the column header. It basically determines the number of columns required (i.e. the maximum distinct values for Link per ref/Name) then Pivots the values into these columns.

Upvotes: 5

Related Questions