Transpose Data Rows to Data Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transpose Data Rows to Data Columns

Author

Topic

zhel04
Starting Member

38 Posts

Posted - 2011-02-15 : 04:23:20

Hi! I'm a newbie when it comes to transposing Rows to Columns. Can someone help me with this? and the 24 months query. Please see below:

The Table that i have:
Date Qty Code Name
01/01/2009 3 123 A
02/01/2009 5 321 B
03/01/2009 9 654 D
...
...
...
12/01/2009 6 985 T
01/01/2010 10 456 K
02/01/2010 15 568 L
...
...
...
12/01/2010 13 693 P

Expected output must be:
Name Code Qty1(Jan2009) Qty2(Feb2009)..... Qty13(Jan2010)... Qty24(Dec2010)

I have to get the values for the 24 months. What can i do or what query can i pass so that the data will know that the next month which is Jan is the 13th month and so on..

Hope can someone help me. I badly need help.

Thanks.

zhel04
Starting Member

38 Posts

Posted - 2011-02-15 : 04:31:36

Here's the recent code that i have but the problem is it can only get the 12 months. How will i add up to the query that the next month will be the 13th month.

SELECT Code, Name,
SUM(CASE WHEN Month(Date) = 1 THEN Qty ELSE 0 END) As Qty1,
SUM(CASE WHEN Month(Date) = 2 THEN Qty ELSE 0 END) As Qty2,
SUM(CASE WHEN Month(Date) = 3 THEN Qty ELSE 0 END) As Qty3,
SUM(CASE WHEN Month(Date) = 4 THEN Qty ELSE 0 END) As Qty4,
SUM(CASE WHEN Month(Date) = 5 THEN Qty ELSE 0 END) As Qty5,
SUM(CASE WHEN Month(Date) = 6 THEN Qty ELSE 0 END) As Qty6,
SUM(CASE WHEN Month(Date) = 7 THEN Qty ELSE 0 END) As Qty7,
SUM(CASE WHEN Month(Date) = 8 THEN Qty ELSE 0 END) As Qty8,
SUM(CASE WHEN Month(Date) = 9 THEN Qty ELSE 0 END) As Qty9,
SUM(CASE WHEN Month(Date) = 10 THEN Qty ELSE 0 END) As Qty10,
SUM(CASE WHEN Month(Date) = 11 THEN Qty ELSE 0 END) As Qty11,
SUM(CASE WHEN Month(Date) = 12 THEN Qty ELSE 0 END) As Qty12
FROM TblABC WHERE Month(date) BETWEEN '1' AND '12'
Group By Code, Name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-15 : 04:35:07

find the number of month between the Date and the 1st date

example :


sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-01-01', [Date]) = 1 then Qty else 0 end) as Qty2

if you are using SQL 2005 / 2008, you can also use the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

zhel04
Starting Member

38 Posts

Posted - 2011-02-15 : 04:59:27

thanks for the reply.. But the output of the code that you gave is almost the same with what i have. Qty 1-12 is ok I just have to know how will i get the 13th month. I was thinking if I'll just do it through Stored proc do you think it is better? I'm just new and I really want to learn.. I'm not familiar with the PIVOT Operator.. :(

quote:
Originally posted by khtan

find the number of month between the Date and the 1st date

example :

sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-01-01', [Date]) = 1 then Qty else 0 end) as Qty2

if you are using SQL 2005 / 2008, you can also use the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

shamasm
Starting Member

11 Posts

Posted - 2011-02-15 : 05:15:57

now try it out
sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-02-01', [Date]) = 1 then Qty else 0 end) as Qty2,
sum(case when datediff(month, '2009-03-01', [Date]) = 1 then Qty else 0 end) as Qty3,
sum(case when datediff(month, '2009-04-01', [Date]) = 1 then Qty else 0 end) as Qty4,
.
.
.
as follows

shamasm
Starting Member

11 Posts

Posted - 2011-02-15 : 05:17:06

sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-02-01', [Date]) = 1 then Qty else 0 end) as Qty2,
sum(case when datediff(month, '2009-03-01', [Date]) = 2 then Qty else 0 end) as Qty3,
sum(case when datediff(month, '2009-04-01', [Date]) = 3 then Qty else 0 end) as Qty4,

zhel04
Starting Member

38 Posts

Posted - 2011-02-15 : 05:21:32

Hi there. Thanks for replying.. I forgot to tell you guys that I shouldn't do the dates in a hard code way cause there is a possibility that the data will be added with the recent years. Thanks again.I hope someone can help me.

quote:
Originally posted by shamasm

sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-02-01', [Date]) = 1 then Qty else 0 end) as Qty2,
sum(case when datediff(month, '2009-03-01', [Date]) = 2 then Qty else 0 end) as Qty3,
sum(case when datediff(month, '2009-04-01', [Date]) = 3 then Qty else 0 end) as Qty4,

Sachin.Nand

2937 Posts

Posted - 2011-02-15 : 06:20:32

quote:
Originally posted by zhel04

Hi there. Thanks for replying.. I forgot to tell you guys that I shouldn't do the dates in a hard code way cause there is a possibility that the data will be added with the recent years. Thanks again.I hope someone can help me.
quote:
Originally posted by shamasm

sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-02-01', [Date]) = 1 then Qty else 0 end) as Qty2,
sum(case when datediff(month, '2009-03-01', [Date]) = 2 then Qty else 0 end) as Qty3,
sum(case when datediff(month, '2009-04-01', [Date]) = 3 then Qty else 0 end) as Qty4,

So what happens if you have 2 records with same value but different year example :'2009-01-01' and '2010-01-01'

PBUH

zhel04
Starting Member

38 Posts

Posted - 2011-02-15 : 06:28:45

sorry for the cross post.. Thanks for the reply. There could be a possibility that there will be an added entries for the current year.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-15 : 07:16:46

quote:
Originally posted by zhel04

thanks for the reply.. But the output of the code that you gave is almost the same with what i have. Qty 1-12 is ok I just have to know how will i get the 13th month. I was thinking if I'll just do it through Stored proc do you think it is better? I'm just new and I really want to learn.. I'm not familiar with the PIVOT Operator.. :(
quote:
Originally posted by khtan

find the number of month between the Date and the 1st date

example :

sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-01-01', [Date]) = 1 then Qty else 0 end) as Qty2

if you are using SQL 2005 / 2008, you can also use the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]


select 
sum(case when datediff(month, '2009-01-01', [Date]) = 0 then Qty else 0 end) as Qty1,
sum(case when datediff(month, '2009-01-01', [Date]) = 1 then Qty else 0 end) as Qty2,
. . . 
sum(case when datediff(month, '2009-01-01', [Date]) = 13 then Qty else 0 end) as Qty13
from  FROM TblABC 
WHERE date between '2009-01-01' and '2010-12-31'

KH
[spoiler]Time is always against us[/spoiler]

Sachin.Nand

2937 Posts

Posted - 2011-02-15 : 07:34:30

I think what you need here is a dynamic pivot.

Try this


declare @tbl table(Date  datetime,  Qty int)

insert into @tbl

select '01-Jan-2009 ',10 union 
select '02-feb-2009 ',5 union 
select '10-feb-2009 ',10 union 
select '02-Jan-2010 ',20 union 
select '02-feb-2010 ',10 


select * from @tbl

declare @collist as varchar(max)=''

select @collist=@collist + 

',[' + 'Qty' + convert(varchar(10), MONTH(date))  + '(' +  convert(varchar(10), datename(mm,date))
+ ' ' + datename(yy,date) +')]' from @tbl
group by MONTH(date),datename(mm,date),datename(yy,date)

declare @sql as varchar(max)=''

Create table #cte(Qty int,Date varchar(100))
Insert #cte
select Qty,'' + 'Qty' + convert(varchar(10), MONTH(date))  + '(' +  convert(varchar(10), datename(mm,date))
+ ' ' + datename(yy,date) +')'Date from @tbl

select @sql='select * from 

(

select Qty,Date from #cte

)u pivot

(sum(Qty) for Date in(' + stuff(@collist,1,1,'') + '))v'

exec (@sql)

drop table #cte

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-15 : 07:46:47

dynamic sql is not required if the result is always for 24 months

KH
[spoiler]Time is always against us[/spoiler]

Sachin.Nand

2937 Posts

Posted - 2011-02-15 : 08:14:53

quote:
Originally posted by khtan

dynamic sql is not required if the result is always for 24 months

KH
[spoiler]Time is always against us[/spoiler]

Yes I missed on that one...

PBUH

zhel04
Starting Member

38 Posts

Posted - 2011-02-16 : 00:37:55

Thank you for helping me out guys.. I guess i have to make a stored procedure for this. The idea now will be like this: There will be an input date from that input that will be the base date to start with the 1st unit. Can someone help me on how to make the stored procedure from the code i come up with?

--Code Snippet--
SELECT Code, Name,
SUM(CASE WHEN Month(Date) = 1 THEN Qty ELSE 0 END) As Qty1,
SUM(CASE WHEN Month(Date) = 2 THEN Qty ELSE 0 END) As Qty2,
SUM(CASE WHEN Month(Date) = 3 THEN Qty ELSE 0 END) As Qty3,
SUM(CASE WHEN Month(Date) = 4 THEN Qty ELSE 0 END) As Qty4,
SUM(CASE WHEN Month(Date) = 5 THEN Qty ELSE 0 END) As Qty5,
SUM(CASE WHEN Month(Date) = 6 THEN Qty ELSE 0 END) As Qty6,
SUM(CASE WHEN Month(Date) = 7 THEN Qty ELSE 0 END) As Qty7,
SUM(CASE WHEN Month(Date) = 8 THEN Qty ELSE 0 END) As Qty8,
SUM(CASE WHEN Month(Date) = 9 THEN Qty ELSE 0 END) As Qty9,
SUM(CASE WHEN Month(Date) = 10 THEN Qty ELSE 0 END) As Qty10,
SUM(CASE WHEN Month(Date) = 11 THEN Qty ELSE 0 END) As Qty11,
SUM(CASE WHEN Month(Date) = 12 THEN Qty ELSE 0 END) As Qty12
FROM TblABC WHERE Month(date) BETWEEN '1' AND '12'
Group By Code, Name

Thank you so much in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-16 : 01:55:05

quote:
Originally posted by zhel04

Thank you for helping me out guys.. I guess i have to make a stored procedure for this. The idea now will be like this: There will be an input date from that input that will be the base date to start with the 1st unit. Can someone help me on how to make the stored procedure from the code i come up with?

Have you got the query working ? using a Stored Procedure or not does no matter. You still need to get your query working to give the result that you wanted.

KH
[spoiler]Time is always against us[/spoiler]

zhel04
Starting Member

38 Posts

Posted - 2011-02-16 : 02:13:26

yes, i was able to get it.. but i have to put in a stored procedure for there is an input from the interface. Thanks KH for helping me out..

quote:
Originally posted by khtan

quote:
Originally posted by zhel04

Thank you for helping me out guys.. I guess i have to make a stored procedure for this. The idea now will be like this: There will be an input date from that input that will be the base date to start with the 1st unit. Can someone help me on how to make the stored procedure from the code i come up with?

Have you got the query working ? using a Stored Procedure or not does no matter. You still need to get your query working to give the result that you wanted.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-16 : 02:28:45

[code]
create procedure <stored procedure name>
@input_1 varchar(10),
@input_2 int
as
begin
<the query here>
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

zhel04
Starting Member

38 Posts

Posted - 2011-02-16 : 02:48:16

thanks KH.. :) I still have a problem though...

quote:
Originally posted by khtan

create procedure <stored procedure name>
@input_1 varchar(10),
@input_2 int
as
begin
<the query here>
end

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-16 : 03:00:53

What's the problem ?

KH
[spoiler]Time is always against us[/spoiler]

zhel04
Starting Member

38 Posts

Posted - 2011-02-17 : 02:26:34

Hi guys I need help.. Now the problem is How would I be able to get the qty on QUARTERLY(8 Quarters)? And how would post that the following month will fall in to the 13th month and so on? Here's the catch no hard code of year and it will be based on the input date.

Thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources