SQL Server Management Studio 2012 Pivot/Cross Tab Query -

i'm using sql server management studio 2012 , create pivot/cross tab query table on 2300 rows.

the table has 5 columns:

 - name  - group  - status  - date  - count 

there 580 distinct names.

each name associated 4 different groups (a, b, c, , d).

each group has complete status of yes or no.

a date associated each status when completed. otherwise, status null.

the count column applicable group b , d , integer value.

sample a:

name    group  status   date    count a.a.1         yes     5/23    null a.a.1     b     yes     5/27    112 a.a.1     c     yes     6/4     null a.a.1     d     yes     6/15    122 a.b.2         yes     5/25    null a.b.2     b     yes     6/1     119 a.b.2     c     no      null    null a.b.2     d     no      null    null 

i trying display status of each name field values across 11 columns :

 - name  - group  - group date  - group b  - group b date  - group b count  - group c  - group c date  - group d  - group d date  - group d count 

the 'name' column have 580 distinct names corresponding group data across a, b, c, , d.

sample b:

nm      grp_a   a_day   grp_b   b_day   b_ct    grp_c   c_day   grp_d   d_day   d_ct a.a.1   yes     5/23    yes     5/27    112     yes     6/4     yes     6/15    122 a.b.2   yes     5/25    yes     6/1     119     no      null    no      null    null 

(column names have been changed fit question section's format)

ultimately, result should have 580 distinct names in first column , corresponding status each group, date of completion (or null if has not been completed yet), , count groups b , d.

i've tried using case statement, generates names once each group, resulting in original table being spaced out across 11 coulmns.

sample c:

nm     grp_a    a_day   grp_b   b_day   b_ct    grp_c   c_day   grp_d   d_day   d_ct a.a.1   yes     5/23                                 a.a.1                    yes    5/27    112          a.a.1                                             yes   6/4          a.a.1                                                            yes    6/15    122 a.b.2   yes     5/25                                 a.b.2                    yes    6/1     119          a.b.2                                             no    null             a.b.2                                                             no    null    null 

what doing wrong? please help!

-- k-moj

without seeing query guessing if trying pivot data case expression suggestion add aggregate function around case.

select   name,   max(case when [group] = 'a' status end) grp_a,   max(case when [group] = 'a' date end) a_day,   max(case when [group] = 'a' [count] end) a_ct,   max(case when [group] = 'b' status end) grp_b,   max(case when [group] = 'b' date end) b_day,   max(case when [group] = 'b' [count] end) b_ct,   max(case when [group] = 'c' status end) grp_c,   max(case when [group] = 'c' date end) c_day,   max(case when [group] = 'c' [count] end) c_ct,   max(case when [group] = 'd' status end) grp_d,   max(case when [group] = 'd' date end) d_day,   max(case when [group] = 'd' [count] end) d_ct yourtable group name 

see sql fiddle demo.

if want use pivot function need first @ unpivoting status, date , count columns first, pivot them in the final result.

an unpivot when convert multiple columns of data multiple rows. can unpivot status, date , count columns using variety of methods. since using sql server 2012 can use cross apply values clause. code convert columns rows be:

select name,    col = col+'_'+[group],    value yourtable  cross apply (   values      ('grp', status),     ('day', [date]),     ('ct', cast([count] varchar(10))) ) c(col, value) 

see demo. gives result:

|  name |   col |  value | | a.a.1 | grp_a |    yes | | a.a.1 | day_a |   5/23 | | a.a.1 |  ct_a | (null) | | a.a.1 | grp_b |    yes | | a.a.1 | day_b |   5/27 | | a.a.1 |  ct_b |    112 | 

instead of having multiple columns want pivot, have values turned new columns in value , new column names in col. can apply pivot function full code similar following:

select name,   grp_a, day_a, ct_a,   grp_b, day_b, ct_b,   grp_c, day_c, ct_c,   grp_d, day_d, ct_d (   select name,      col = col+'_'+[group],      value   yourtable    cross apply   (     values        ('grp', status),       ('day', [date]),       ('ct', cast([count] varchar(10)))   ) c(col, value) ) d pivot (   max(value)   col in (grp_a, day_a, ct_a,               grp_b, day_b, ct_b,               grp_c, day_c, ct_c,               grp_d, day_d, ct_d) ) piv 

see sql fiddle demo