Convert rows to Colum - SQL Server Forums

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

Convert rows to Colum

Author

Topic

Amjath
Yak Posting Veteran

66 Posts

Posted - 2007-08-28 : 04:11:10

Hi All,

I'm having this many rows(around 25 columns am only showing some 3),
but in the output i want only 2 rows, how can i do that.

1 6_00 SB3215-02
1 6_01 00301A39A041
1 6_02 Successful
1 6_03 Successful
1 6_04 Successful
1 6_05 00301A39A042
1 8_00 Pass
1 8_02 00301A39A041
1 8_03 00904BC1A1D6
1 8_32 5.18
1 8_33 5.19
1 8_34 5.24
1 8_36 5.40
1 8_37 5.39
1 8_38 5.32
1 8_40 -49
1 8_41 -56
1 8_42 -53
1 8_44 -62
1 8_45 -63
1 8_46 -60

2 6_00 SB3216-02
2 6_01 00301A39A041
2 6_02 Successful
2 6_03 Successful
2 6_04 Successful
2 6_05 00301A39A042
2 8_00 Pass
2 8_02 00301A39A041
2 8_03 00904BC1A1D6
2 8_32 5.68
2 8_33 6.19
2 8_34 9.24
2 8_36 5.40
2 8_37 5.89
2 8_38 5.32
2 8_40 -49
2 8_41 -56
2 8_42 -53
2 8_44 -82
2 8_45 -63
2 8_46 -69

Output like this
----------------

1 6_00 SB3215-02 6_01 00301A39A041 6_02 Successful 6_03 Successful 6_04 Successful 6_05 00301A39A042 8_00 Pass 8_02 00301A39A041 8_03 00904BC1A1D6 8_32 5.18 8_33 5.19 8_34 5.24 ....................

like this

please help me, how can i do this

with Regards
Amjath

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 05:20:40

search for cross tabs or pivot here.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Amjath
Yak Posting Veteran

66 Posts

Posted - 2007-08-28 : 05:31:32

Thanks for your information.

how cross tab will solve my problem....
plz explain me

With Regards
Amjath

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 05:36:11

Where do you wanto to show data?

Madhivanan

Failing to plan is Planning to fail

Amjath
Yak Posting Veteran

66 Posts

Posted - 2007-08-28 : 05:39:20

Hi madhivanan,

In DataGridView(grid in c#).

With Regards
Amjath

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-28 : 05:45:08

look here:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Amjath
Yak Posting Veteran

66 Posts

Posted - 2007-08-28 : 05:48:24

here i got some error like this
Incorrect syntax near the keyword 'Pivot'.

when i click on this it will show here.
create table #temp (Pivot varchar(100))

with Regards
Amjath

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-28 : 05:57:41

I think Pivot is the keyword in sql server 2005. Try using pivots

Madhivanan

Failing to plan is Planning to fail

ser_eng
Starting Member

3 Posts

Posted - 2007-10-16 : 04:04:23

Hi,

I'm having problem in converting the data from rows into column and in dire of your help.

Example:
currently i'm having a table

Item Qty Mth
---- ----- -----
A 10 2006-1
A 40 2006-3
B 20 2006-2
C 30 2006-3
C 50 2006-2

How can I change it to the output I want?

Output:
Item Jan Feb Mar
---- ----- ----- -----
A 10 - 40
B - 20 -
C - 50 30

Please advise and sorry for the alignment. Thanks.

Cheers,
Serene

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 04:20:50

[code]DECLARE @Sample TABLE (Item CHAR(1), Qty TINYINT, Mth CHAR(6))

INSERT @Sample
SELECT 'A', 10, '2006-1' UNION ALL
SELECT 'A', 40, '2006-3' UNION ALL
SELECT 'B', 20, '2006-2' UNION ALL
SELECT 'C', 30, '2006-3' UNION ALL
SELECT 'C', 50, '2006-2'

SELECT Item,
LEFT(Mth, 4) AS [Year],
SUM(CASE WHEN RIGHT(Mth, 2) = '-1' THEN Qty ELSE 0 END) AS [January],
SUM(CASE WHEN RIGHT(Mth, 2) = '-2' THEN Qty ELSE 0 END) AS [Fabruary],
SUM(CASE WHEN RIGHT(Mth, 2) = '-3' THEN Qty ELSE 0 END) AS [March],
SUM(CASE WHEN RIGHT(Mth, 2) = '-4' THEN Qty ELSE 0 END) AS [April],
SUM(CASE WHEN RIGHT(Mth, 2) = '-5' THEN Qty ELSE 0 END) AS [May],
SUM(CASE WHEN RIGHT(Mth, 2) = '-6' THEN Qty ELSE 0 END) AS [June],
SUM(CASE WHEN RIGHT(Mth, 2) = '-7' THEN Qty ELSE 0 END) AS [July],
SUM(CASE WHEN RIGHT(Mth, 2) = '-8' THEN Qty ELSE 0 END) AS [August],
SUM(CASE WHEN RIGHT(Mth, 2) = '-9' THEN Qty ELSE 0 END) AS [September],
SUM(CASE WHEN RIGHT(Mth, 3) = '-10' THEN Qty ELSE 0 END) AS [October],
SUM(CASE WHEN RIGHT(Mth, 3) = '-11' THEN Qty ELSE 0 END) AS [November],
SUM(CASE WHEN RIGHT(Mth, 3) = '-12' THEN Qty ELSE 0 END) AS [December]
FROM @Sample
GROUP BY Item,
LEFT(Mth, 4)
ORDER BY Item,
LEFT(Mth, 4)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 04:21:26

[code]SELECT Item,
SUM(CASE WHEN RIGHT(Mth, 2) = '-1' THEN Qty ELSE 0 END) AS [January],
SUM(CASE WHEN RIGHT(Mth, 2) = '-2' THEN Qty ELSE 0 END) AS [Fabruary],
SUM(CASE WHEN RIGHT(Mth, 2) = '-3' THEN Qty ELSE 0 END) AS [March],
SUM(CASE WHEN RIGHT(Mth, 2) = '-4' THEN Qty ELSE 0 END) AS [April],
SUM(CASE WHEN RIGHT(Mth, 2) = '-5' THEN Qty ELSE 0 END) AS [May],
SUM(CASE WHEN RIGHT(Mth, 2) = '-6' THEN Qty ELSE 0 END) AS [June],
SUM(CASE WHEN RIGHT(Mth, 2) = '-7' THEN Qty ELSE 0 END) AS [July],
SUM(CASE WHEN RIGHT(Mth, 2) = '-8' THEN Qty ELSE 0 END) AS [August],
SUM(CASE WHEN RIGHT(Mth, 2) = '-9' THEN Qty ELSE 0 END) AS [September],
SUM(CASE WHEN RIGHT(Mth, 3) = '-10' THEN Qty ELSE 0 END) AS [October],
SUM(CASE WHEN RIGHT(Mth, 3) = '-11' THEN Qty ELSE 0 END) AS [November],
SUM(CASE WHEN RIGHT(Mth, 3) = '-12' THEN Qty ELSE 0 END) AS [December]
FROM @Sample
WHERE Mth LIKE '2006%'
GROUP BY Item
ORDER BY Item[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 04:24:07

Is 10 - 40 just the Min and Max values, or must the intervening values be present?

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 04:28:40

Blinking formatting [lack of]. I didn't see the Month columns, thought it was a "Display the range" thingie.

There ought to be a Test to pass before being allowed to drive ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 04:32:31

I prefer working on dates



SELECT		Item,
		year(mth)AS [Year],
		SUM(CASE WHEN month(mth)= '1' THEN Qty ELSE 0 END) AS [January],
		SUM(CASE WHEN month(mth)= '2' THEN Qty ELSE 0 END) AS [Fabruary],
		SUM(CASE WHEN month(mth)= '3' THEN Qty ELSE 0 END) AS [March],
		SUM(CASE WHEN month(mth)= '4' THEN Qty ELSE 0 END) AS [April],
		SUM(CASE WHEN month(mth)= '5' THEN Qty ELSE 0 END) AS [May],
		SUM(CASE WHEN month(mth)= '6' THEN Qty ELSE 0 END) AS [June],
		SUM(CASE WHEN month(mth)= '7' THEN Qty ELSE 0 END) AS [July],
		SUM(CASE WHEN month(mth)= '8' THEN Qty ELSE 0 END) AS [August],
		SUM(CASE WHEN month(mth)= '9' THEN Qty ELSE 0 END) AS [September],
		SUM(CASE WHEN month(mth) = '10' THEN Qty ELSE 0 END) AS [October],
		SUM(CASE WHEN month(mth) = '11' THEN Qty ELSE 0 END) AS [November],
		SUM(CASE WHEN month(mth) = '12' THEN Qty ELSE 0 END) AS [December]
FROM		(select item, qty,cast(mth+'-01' as datetime) as mth from @Sample) as T
GROUP BY	Item,
		year(mth)
ORDER BY	Item,
		year(mth)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 04:46:02

I prefer PIVOT

SELECT	p.Item,
	p.[1] AS [January],
	p.[2] AS [February],
	p.[3] AS [March],
	p.[4] AS [April],
	p.[5] AS [May],
	p.[6] AS [June],
	p.[7] AS [July],
	p. AS [August],
	p.[9] AS [September],
	p.[10] AS [October],
	p.[11] AS [November],
	p.[12] AS [December]
FROM	(
		SELECT	Item,
			Qty,
			PARSENAME(REPLACE(Mth, '-', '.'), 1) AS theMonth
		FROM	@Sample
		WHERE	Mth LIKE '2006%'
	) AS y
PIVOT	(
		SUM(y.Qty) FOR y.theMonth IN ([1], [2], [3], [4], [5], [6], [7], , [9], [10], [11], [12])
	) AS p

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 04:52:28

Yes. Provided OP uses SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

ser_eng
Starting Member

3 Posts

Posted - 2007-10-16 : 06:07:20

quote:

Originally posted by Peso

SELECT		Item,
		SUM(CASE WHEN RIGHT(Mth, 2) = '-1' THEN Qty ELSE 0 END) AS [January],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-2' THEN Qty ELSE 0 END) AS [Fabruary],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-3' THEN Qty ELSE 0 END) AS [March],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-4' THEN Qty ELSE 0 END) AS [April],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-5' THEN Qty ELSE 0 END) AS [May],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-6' THEN Qty ELSE 0 END) AS [June],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-7' THEN Qty ELSE 0 END) AS [July],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-8' THEN Qty ELSE 0 END) AS [August],
		SUM(CASE WHEN RIGHT(Mth, 2) = '-9' THEN Qty ELSE 0 END) AS [September],
		SUM(CASE WHEN RIGHT(Mth, 3) = '-10' THEN Qty ELSE 0 END) AS [October],
		SUM(CASE WHEN RIGHT(Mth, 3) = '-11' THEN Qty ELSE 0 END) AS [November],
		SUM(CASE WHEN RIGHT(Mth, 3) = '-12' THEN Qty ELSE 0 END) AS [December]
FROM		@Sample
WHERE		Mth LIKE '2006%'
GROUP BY	Item
ORDER BY	Item

E 12°55'05.25"
N 56°04'39.16"

Hi Peso, Thanks for your fast reply. Actually I'm using SQL server 2000 and when I'm trying the queries above, I've got an error msg "cannot perform an aggregate function on an expression containing an aggregate or a subquery"

Queries I'm using is:

SELECT T1.ItemCode,

SUM(CASE WHEN RIGHT(T2.Code, 2) = '-1' THEN sum(T1.Quantity) ELSE 0 END) AS [January],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-2' THEN sum(T1.Quantity) ELSE 0 END) AS [Fabruary],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-3' THEN sum(T1.Quantity) ELSE 0 END) AS [March],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-4' THEN sum(T1.Quantity) ELSE 0 END) AS [April],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-5' THEN sum(T1.Quantity) ELSE 0 END) AS [May],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-6' THEN sum(T1.Quantity) ELSE 0 END) AS [June],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-7' THEN sum(T1.Quantity) ELSE 0 END) AS [July],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-8' THEN sum(T1.Quantity) ELSE 0 END) AS [August],
SUM(CASE WHEN RIGHT(T2.Code, 2) = '-9' THEN sum(T1.Quantity) ELSE 0 END) AS [September],
SUM(CASE WHEN RIGHT(T2.Code, 3) = '-10' THEN sum(T1.Quantity) ELSE 0 END) AS [October],
SUM(CASE WHEN RIGHT(T2.Code, 3) = '-11' THEN sum(T1.Quantity) ELSE 0 END) AS [November],
SUM(CASE WHEN RIGHT(T2.Code, 3) = '-12' THEN sum(T1.Quantity) ELSE 0 END) AS [December]

FROM SAMPLE T0 INNER JOIN SAMPLE1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN SAMPLE T2 ON T0.FinPod = T2.AbsEntry

WHERE T0.Customer ='[%A]'
GROUP BY T1.ItemCode

Please advise. Thanks for your help and time.

p/s: madhivanan & kristen thanks for your help :p

cheers,
Serene

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-16 : 06:13:11

Sum-A-Sum or Rama-Lama-Ding-Dong?

SELECT		T1.ItemCode, 
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-1' THEN T1.Quantity ELSE 0 END) AS [January],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-2' THEN T1.Quantity ELSE 0 END) AS [Fabruary],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-3' THEN T1.Quantity ELSE 0 END) AS [March],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-4' THEN T1.Quantity ELSE 0 END) AS [April],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-5' THEN T1.Quantity ELSE 0 END) AS [May],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-6' THEN T1.Quantity ELSE 0 END) AS [June],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-7' THEN T1.Quantity ELSE 0 END) AS [July],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-8' THEN T1.Quantity ELSE 0 END) AS [August],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-9' THEN T1.Quantity ELSE 0 END) AS [September],
		SUM(CASE WHEN RIGHT(T2.Code, 3) = '-10' THEN T1.Quantity ELSE 0 END) AS [October],
		SUM(CASE WHEN RIGHT(T2.Code, 3) = '-11' THEN T1.Quantity ELSE 0 END) AS [November],
		SUM(CASE WHEN RIGHT(T2.Code, 3) = '-12' THEN T1.Quantity ELSE 0 END) AS [December]
FROM		[SAMPLE] AS T0
INNER JOIN	SAMPLE1 AS T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN	[SAMPLE] AS T2 ON T2.AbsEntry = T0.FinPod
WHERE		T0.Customer LIKE '%A'
GROUP BY	T1.ItemCode

E 12°55'05.25"
N 56°04'39.16"

ser_eng
Starting Member

3 Posts

Posted - 2007-10-16 : 22:41:53

quote:

Originally posted by Peso

Sum-A-Sum or Rama-Lama-Ding-Dong?

SELECT		T1.ItemCode, 
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-1' THEN T1.Quantity ELSE 0 END) AS [January],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-2' THEN T1.Quantity ELSE 0 END) AS [Fabruary],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-3' THEN T1.Quantity ELSE 0 END) AS [March],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-4' THEN T1.Quantity ELSE 0 END) AS [April],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-5' THEN T1.Quantity ELSE 0 END) AS [May],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-6' THEN T1.Quantity ELSE 0 END) AS [June],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-7' THEN T1.Quantity ELSE 0 END) AS [July],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-8' THEN T1.Quantity ELSE 0 END) AS [August],
		SUM(CASE WHEN RIGHT(T2.Code, 2) = '-9' THEN T1.Quantity ELSE 0 END) AS [September],
		SUM(CASE WHEN RIGHT(T2.Code, 3) = '-10' THEN T1.Quantity ELSE 0 END) AS [October],
		SUM(CASE WHEN RIGHT(T2.Code, 3) = '-11' THEN T1.Quantity ELSE 0 END) AS [November],
		SUM(CASE WHEN RIGHT(T2.Code, 3) = '-12' THEN T1.Quantity ELSE 0 END) AS [December]
FROM		[SAMPLE] AS T0
INNER JOIN	SAMPLE1 AS T1 ON T1.DocEntry = T0.DocEntry
INNER JOIN	[SAMPLE] AS T2 ON T2.AbsEntry = T0.FinPod
WHERE		T0.Customer LIKE '%A'
GROUP BY	T1.ItemCode

E 12?5'05.25"
N 56?4'39.16"

Hi Peso,

Thanks again for your fast reply. I¡¦ve tried the queries and it¡¦s error free with some problem. The quantity display for every mth is ¡§0¡¨.

Actually my scenario is:
- item A sold in 01/01/07 = 10 kg
- item A sold in 10/01/07 = 20 kg
total item A sold in ¡§JAN¡¨ = 30 kg

- item A sold in 01/03/07 = 5 kg
- item A sold in 21/03/07 = 10 kg
total item A sold in ¡§MAR¡¨ = 15kg

At first, I did managed to sum up the quantity but it display as row (in which I need it as column) and the output is:

Item Qty Period
----- ----- ---------
A 30 2007-01
A 15 2007-03

I¡¦ve tried the queries you propose and managed to get the period as column (but qty = 0) and the output is:

Item Jan Feb Mar
----- ----- ----- -----
A 0 0 0

I¡¦m not sure what¡¦s went wrong. Pleas advise, any help is much appreciated. Thanks for all your time.

Cheers,
serene

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources