0

I have this table as input

A        B       C
11001   1011    873824
11001   1012    2240
11002   1011    36792
11002   1012    557172
11003   1061    2970

I need this output using pivot

A           1011    1012    1061
11001       873824  2240
11002       36792   557172  
11003                       2970
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
J.Khan
  • 9
  • 3
  • 2
    Welcome to Stack Overflow! Please [take the tour](http://stackoverflow.com/tour), have a look at the help center, in particular [how to ask a good question?](http://stackoverflow.com/help/how-to-ask). What part are you stuck on? What have you tried? – Maciej Lach Mar 16 '16 at 12:43
  • Also, please provide your sample data in the following form: `declare @t table (A int, B int, C int); insert into @t (A, B, C) values (11001, 1011, 873824), 11001, 1012, 2240), etc...` – Ralph Mar 16 '16 at 12:48

2 Answers2

1

You can use a Case expression.

Query

select A,
max(case B when 1011 then C end) as [1011],
max(case B when 1012 then C end) as [1012],
max(case B when 1061 then C end) as [1061]
from your_table_name
group by A;

or you can use a dynamic sql query also.

Query

declare @sql as varchar(max);

select @sql = 'select A, ' + stuff(
    (
        select distinct ',max(case B when ' + cast(B as varchar(50)) + ' 
               then C end) as [' + cast(B as varchar(50)) + ']'
        from your_table_name
        for xml path, type).value('.[1]', 'nvarchar(max)'),
    1, 1, ''
);

select @sql += ' from your_table_name group by A;'
execute (@sql);

Note : Assuming that your column datatype as INT.

Result

+=======+========+========+======+
| A     | 1011   | 1012   | 1061 |
+=======+========+========+=======+
| 11001 | 873824 | 2240   |      |
+-------+--------+--------+------+
| 11002 | 36792  | 557172 |      |
+-------+--------+--------+------+
| 11003 |        |        | 2970 |
+=======+========+========+======+
Ullas
  • 11,450
  • 4
  • 33
  • 50
0

Please try the below query

select A,[1011],[1012],[1061] from 
(select * from t)s
pivot
(max(C) for B in ([1011],[1012],[1061]))p

here's a link to demo fiddle :http://sqlfiddle.com/#!6/93e1e/1

Explanation:

The above query uses the simple PIVOT syntax. Here all data from columns A, B, and C is selected in source set (aliased as s) and then pivoted on column B for values of column C.

Helpful link: Read more on msdn on PIVOT https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Dynamic SQL:

--create table t (A int, B int, C int);
--insert into t values (11001,1011,873824)
--,(11001,1012,2240)
--,(11002,1011,36792)
--,(11002,1012,557172)
--,(11003,1061,2970);

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',[' + CAST(B AS NVARCHAR) +']' FROM t FOR XML PATH('')) ,1,1,'')

SET @query='select A,' + @cols + ' from '+
 '(select * from t)s '+
 'pivot '+
 '(max(C) for B in ('+@cols +'))p '

EXEC(@query)

--drop table t
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60