SQL Join Query - Group By / Aggregation

Question

I realise this question has been asked before in many forms but I am struggling to apply every answer I've found to my specific table. To start - straight to the details.

Table A has four columns (Unique ID, Number, Revision, Date). When executing a select * from A, the result set is as follows -

ID | Number | Revision | Date
------------------------------------
01 | 12345  | 1        | 01/01/2010
02 | 12345  | 2        | 01/04/2010
03 | 123    | 1        | 01/12/2010
04 | 1234   | 1        | 09/09/2012
05 | 12345  | 3        | 09/12/2012

I would like the dates for each Revision in a single row per Number, e.g. for Number 12345, the query would return

ID | Number | Revision | Date | Revision 2 | Revision 2 Date | Revision 3 | Revision 3 Date |
---------------------------------------------------------------------------------------------
01 | 12345  | 1 | 01/01/2010 |2  | 01/04/2010      | 3 |  09/12/2012|

I understand the syntax would be similar to

SELECT Revision, Date 
FROM (
SELECT NUMBER 
FROM A) as B INNER JOIN ON a.Number = B.Number 
GROUP BY a.Number

However this still returns a row for each combination, I would like only the results shown above.

I would appreciate any suggestions or hints!

Did you see this post? stackoverflow.com/questions/1187460/sql-rows-to-columns — martinwnet
– martinwnet, Commented Oct 8, 2012 at 13:06
Pivot is what you need to turn your Revisions and Dates into columns msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Mikey Mouse
– Mikey Mouse, Commented Oct 8, 2012 at 13:06

Taryn · Accepted Answer · 2012-10-08 13:55:42Z

2

You can use both an UNPIVOT and then a PIVOT to accomplish this. If you have a known number of values to convert to column, then you can use a STATIC PIVOT and hard-code the values:

select number, [revision_1], [date_1],
             [revision_2], [date_2],
             [revision_3], [date_3]
from
(
  select number, value,
    col + '_' + cast(rn as varchar(10)) col
  from
  (
    select id, number, cast(revision as varchar(10)) revision, 
      convert(varchar(10), date, 120) date,
      row_number() over(partition by number order by date) rn
    from yourtable
  ) x
  unpivot
  (
    value
    for col in (revision, date)
  ) u
) x1
pivot
(
  max(value)
  for col in ([revision_1], [date_1],
             [revision_2], [date_2],
             [revision_3], [date_3])
)p

See SQL Fiddle with Demo

If you have an unknown number of values, then you will need to use dynamic SQL to PIVOT the data.

DECLARE @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)


select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name + '_'
                         + cast(t.rn as varchar(10)))
                    from
                    (
                      select row_number()
                        over(partition by number order by date) rn
                      from yourtable
                    ) t
                    cross apply 
                      sys.columns as C
                   where C.object_id = object_id('yourtable') and
                         C.name in ('Revision', 'date')
                   group by c.name, t.rn
                   order by t.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
      from
      (
          select number, value,
            col + ''_'' + cast(rn as varchar(10)) col
        from 
        (
          select id, number, cast(revision as varchar(10)) revision, 
            convert(varchar(10), date, 120) date,
            row_number() over(partition by number order by date) rn
          from yourtable
        ) x
        unpivot
        (
          value
          for col in (revision, date)
        ) u
      ) x1
      pivot
      (
        max(value)
        for col in ('+ @colspivot +')
      ) p'

exec(@query)

See SQL Fiddle With demo

edited Oct 8, 2012 at 13:55

answered Oct 8, 2012 at 13:14

Taryn

249k57 gold badges374 silver badges409 bronze badges

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

7 Comments

znedw Over a year ago

Excellent! Thank-you, one issue though, every date is null, when I run select convert(varchar(10), date, 120) date from mytable, the dates are fine, however in both of those queries (the second dynamic one, there is no date columns at all) dates are all null.

Taryn Over a year ago

@user1142132 can you create a SQL Fiddle with some sample data for me to look at? Using your sample I did not get any null dates -- sqlfiddle.com/#!3/67d11/21

znedw Over a year ago

I've created an sqlfiddle to replicate my table and some sample data and I'm not getting the null dates in it sqlfiddle.com/#!3/8706a/1, however here's a screenshot of my results in sql server i.imgur.com/Qx4oN.png

Taryn Over a year ago

@user1142132 can you create a larger data sample on the sql fiddle? It is hard to tell with only two rows of data

znedw Over a year ago

apologies, I've added a few more, the dates still appear, let me know if you need a larger sample.

|

Collectives™ on Stack Overflow

SQL Join Query - Group By / Aggregation

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related