T-SQL Group Rows Into Columns

Question

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

possible duplicate of How to transform rows to columns

Pondlife
– Pondlife

2012-05-18 12:48:17 +00:00
Commented May 18, 2012 at 12:48 — Pondlife
– Pondlife, Commented May 18, 2012 at 12:48
You're probably looking for a PIVOT or UNPIVOT.

Chris Gessler
– Chris Gessler

2012-05-18 12:49:11 +00:00
Commented May 18, 2012 at 12:49 — Chris Gessler
– Chris Gessler, Commented May 18, 2012 at 12:49

Nikola Markovinović · Accepted Answer · 2012-05-18 13:08:09Z

12

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.

answered May 18, 2012 at 13:08

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GarethD · Accepted Answer · 2012-05-18 13:07:53Z

5

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.

answered May 18, 2012 at 13:07

GarethD

70k11 gold badges90 silver badges130 bronze badges

1 Comment

JBarnes Over a year ago

I have a maximum number of links so the above answer works in this case. However, this is useful for future reference - thanks.

Collectives™ on Stack Overflow

T-SQL Group Rows Into Columns

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related