CODEWITHSUNDEEP
sqlsql-serverpivot
kbvishnu
kbvishnu

Reputation: 15630

Convert Row to Column based on data in SQL

The below image is my table enter image description here

The below excel is the design for output.enter image description here

My table contains 12 columns for each month and an year column. An item, there it can be available in multiple years and data for each month.

year- itemcode- jan- feb

2014-  pqr-      12-  11

2015-  pqr-      4-    8

I need to generated the below output. For an item available for multiple years output needs to list in the following manner.

ItemCode- Jan14- Feb14- Mar14-... Dec14- Jan15- Feb15-... Dec15

pqr-        12-   11-                     4-     8-

How can I able to achieve this. I tried different methods after googling. But I am not able to get proper input for solving this. At present I am trying some solutions found in SO. It will be very helpful someone can give some inputs. Thanks in advance.

Upvotes: 1

Views: 114

Answers (2)

James S
James S

Reputation: 3588

You'll need to use dymanic SQL...

Basically assuming a table called #tbl (with a little bit of sample data - ive only done 3 months but extend to 12!)

CREATE TABLE #tbl ([ItemCode] NVARCHAR(20), [Year] INT, Jan INT, Feb INT, Mar INT)
INSERT #tbl ( ItemCode, Year, Jan, Feb, Mar )
VALUES  ( 'pqr', 2014, 12, 11, 7  ), ( 'pqr', 2015, 4, 8, 0  ), 
  ( 'xyz', 2015, 7, 1, 0  ), ( 'abc', 2013, 63, 23, 12  ), ( 'abc', 2015, 63, 23, 12  )

we want to generate a query that looks like

SELECT tbase.ItemCode
    , ISNULL(t13.Jan,0) AS 'Jan-13', ISNULL(t13.Feb,0) AS 'Feb-13', ISNULL(t13.Mar,0) AS 'Mar-13'
    , ISNULL(t14.Jan,0) AS 'Jan-14', ISNULL(t14.Feb,0) AS 'Feb-14', ISNULL(t14.Mar,0) AS 'Mar-14'
    , ISNULL(t15.Jan,0) AS 'Jan-15', ISNULL(t15.Feb,0) AS 'Feb-15', ISNULL(t15.Mar,0) AS 'Mar-15'  
FROM
    (SELECT DISTINCT(ItemCode) AS ItemCode FROM #tbl) AS tbase 
    LEFT JOIN (SELECT * FROM #tbl AS t13 WHERE YEAR = 2013) AS t13 ON t13.ItemCode = tbase.ItemCode
    LEFT JOIN (SELECT * FROM #tbl AS t14 WHERE YEAR = 2014) AS t14 ON t14.ItemCode = tbase.ItemCode
    LEFT JOIN (SELECT * FROM #tbl AS t15 WHERE YEAR = 2015) AS t15 ON t15.ItemCode = tbase.ItemCode

And results like:

ItemCode    Jan-13  Feb-13  Mar-13  Jan-14  Feb-14  Mar-14  Jan-15  Feb-15  Mar-15
abc         63      23      12      0       0       0       63      23      12
pqr         0       0       0       12      11      7       4       8       0
xyz         0       0       0       0       0       0       7       1       0

As you can see from the query - the 2 things to buld up are the lines , ISNULL(t13.Jan,0)... and LEFT JOIN (SELECT ...

we can do this by declaring 2 NVARCHAR(MAX) variables (one for the select, one for the from) and building them up inside a while loop while iterating through the available years.

ie something like...

DECLARE @select NVARCHAR(MAX);
DECLARE @from NVARCHAR(MAX);
DECLARE @years TABLE(yr INT);
DECLARE @year INT;
DECLARE @yearName NVARCHAR(2)
INSERT @years
SELECT DISTINCT [Year] FROM #tbl

SELECT @year = MIN(yr) FROM @years
SELECT @yearName = RIGHT(CAST(@year AS NVARCHAR(4)),2)

SELECT @select = 'SELECT tbase.ItemCode'
SELECT @from = 'FROM (SELECT DISTINCT(ItemCode) AS ItemCode FROM #tbl) AS tbase '

WHILE EXISTS (SELECT NULL FROM @years WHERE yr = @year)
BEGIN
    SELECT @yearName = RIGHT(CAST(@year AS NVARCHAR(4)),2)
    SELECT @select = @select + CHAR(13) + CHAR(10) 
        + ', ISNULL(t' + @yearName + '.Jan,0) AS [Jan-' + @yearName + '],' 
        + ' ISNULL(t' + @yearName + '.Feb,0) AS [Feb-' + @yearName + '],'-- +9 more
        + ' ISNULL(t' + @yearName + '.Mar,0) AS [Mar-' + @yearName + '] '  
    SELECT @from = @from + CHAR(13) + CHAR(10) 
        + 'LEFT JOIN (SELECT * FROM #tbl AS t' + @yearName 
        + ' WHERE [Year] = ' + CAST(@year AS NVARCHAR(4)) + ') AS t' + @yearName 
        + ' ON t' + @yearName + '.ItemCode = tbase.ItemCode '

    SELECT @year = @year + 1
END

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = @select + CHAR(13) + CHAR(10) + @from
EXEC (@sql)

All you need to do is extend this to the full 12 months and you're done!

NB - I've assumed that there is at least 1 entry for every year. If you have a gap where there is a year in the middle of your range with no entries whatsoever you'd need to do a minor modification to the WHILE loop - ie WHILE @year <= (SELECT MAX(Year) FROM @years)

Upvotes: 1

Nicolas M
Nicolas M

Reputation: 81

Suppose you have the following table :

select 'AA' as ItemCode,2014 as year, 23 as Jan, 55 as Feb, 55 as Mar,565 as Apr,656 as May,
343 as Jun,54 as Jul,23 as Aug,66 as Sep,645 as Oct,32 as Nov,66 as Dec
into dbo.test ;
insert into dbo.test select 'AA',2015,554,456,3,54,756,98,2,765,24,876,34,66
union select 'BB',2014,45,56,3,54,756,98,2,765,24,876,34,66
union select 'BB',2015,45,56,3,54,756,98,2,765,24,876,34,66;

With dynamic sql, do 

declare @sql nvarchar(1000);
declare @sql2 nvarchar(1000);
declare @year int;
declare @first_year int;
declare c cursor for select distinct year from dbo.test;
open c;
FETCH NEXT FROM c into @year
if @@FETCH_STATUS = 0
begin
  select @sql='select test'+convert(varchar,@year)+'.ItemCode';
  select @sql = @sql+',test'+CONVERT(varchar,@year)+'.Jan as Jan'+CONVERT(varchar,@year)+',test'
    +CONVERT(varchar,@year)+'.Feb as Feb'+CONVERT(varchar,@year)+',test'
    +CONVERT(varchar,@year)+'.Mar as Mar'+CONVERT(varchar,@year);
  select @sql2='test test'+CONVERT(varchar,@year);
  select @first_year=@year;
end;
FETCH NEXT FROM c into @year
WHILE @@FETCH_STATUS = 0
begin
  select @sql = @sql+',test'+CONVERT(varchar,@year)+'.Jan as Jan'+CONVERT(varchar,@year)+',test'
    +CONVERT(varchar,@year)+'.Feb as Feb'+CONVERT(varchar,@year)+',test'
    +CONVERT(varchar,@year)+'.Mar as Mar'+CONVERT(varchar,@year);
  select @sql2=@sql2+' inner join test test'+CONVERT(varchar,@year)+' on test'+CONVERT(varchar,@year)+'.ItemCode=test'+CONVERT(varchar,@first_year)+'.ItemCode and test'+CONVERT(varchar,@year)+'.year='+CONVERT(varchar,@year);
  FETCH NEXT FROM c into @year
end;
close c;
deallocate c;
select @sql=@sql+' FROM '+@sql2 + ' AND test'+convert(varchar,@first_year)+'.year='+CONVERT(varchar,@year);
print @sql
EXECUTE sp_executesql @sql;

Or, with standard SQL, something like this

select test2014.ItemCode,test2014.Jan as Jan2014,test2014.Feb as Feb2014,test2015.Jan as Jan2015,test2015.Feb as Feb2015
from test test2014 inner join test test2015 on test2014.ItemCode=test2015.ItemCode
where test2014.year=2014 and test2015.year=2015;

Upvotes: 1

Related Questions