Multi Row to one row (Help) - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Multi Row to one row (Help)

Author

Topic

kenlok
Starting Member

16 Posts

Posted - 2009-04-27 : 04:11:12

Hello,

Help!
I would like to display the table1 into tabel2 record like this.
Please help me to solve the problem.... thanks a lot!

Select * from table1
ColorName Size Qty
-----------------------
Color1 S 30
Color1 M 38
Color1 L 39
Color1 XL 50
Color2 S 30
Color2 M 30
Color2 L 30
Color2 XL 30

Select * from table2

ColorName Size1 Size2 Size3 Size4 Size5 Size6 Size7 Size8 Qty1 Qty2 Qty3 Qty4 Qty5 Qty6 Qty7 Qty8
--------------------------------------------------------------------------------------------------
Color1 S M L XL null null null null 30 38 39 50 null null null null
Color2 S M L XL null null null null 30 30 30 30 null null null null

Script
--------------------------------------------------------------------------------------------------

Create table #table1 (
ColorName varchar(20) ,
Size varchar(12), Qty varchar(12)
)
Create table #table2 (
ColorName varchar(20) ,
Size1 varchar(12), Qty1 varchar(12),
Size2 varchar(12), Qty2 varchar(12),
Size3 varchar(12), Qty3 varchar(12),
Size4 varchar(12), Qty4 varchar(12),
Size5 varchar(12), Qty5 varchar(12),
Size6 varchar(12), Qty6 varchar(12),
Size7 varchar(12), Qty7 varchar(12),
Size8 varchar(12), Qty8 varchar(12)
)

insert into #table1(ColorName, Size, Qty)
values ('Color1','S','30')
insert into #table1(ColorName, Size, Qty)
values ('Color1','M','38')
insert into #table1(ColorName, Size, Qty)
values ('Color1','L','39')
insert into #table1(ColorName, Size, Qty)
values ('Color1','XL','50')
insert into #table1(ColorName, Size, Qty)
values ('Color2','S','30')
insert into #table1(ColorName, Size, Qty)
values ('Color2','M','30')
insert into #table1(ColorName, Size, Qty)
values ('Color2','L','30')
insert into #table1(ColorName, Size, Qty)
values ('Color2','XL','30')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 05:13:02

[code]DECLARE @Sample TABLE
(
ColorName VARCHAR(20) NOT NULL,
Size VARCHAR(2) NOT NULL,
Qty INT NOT NULL
)

INSERT @Sample
SELECT 'Color1', 'S', 30 UNION ALL
SELECT 'Color1', 'M', 38 UNION ALL
SELECT 'Color1', 'L', 39 UNION ALL
SELECT 'Color1', 'XL', 50 UNION ALL
SELECT 'Color2', 'S', 30 UNION ALL
SELECT 'Color2', 'M', 30 UNION ALL
SELECT 'Color2', 'L', 30 UNION ALL
SELECT 'Color2', 'XL', 30

SELECT ColorName,
MAX(CASE WHEN recID = 1 THEN Size ELSE NULL END) AS Size1,
MAX(CASE WHEN recID = 2 THEN Size ELSE NULL END) AS Size2,
MAX(CASE WHEN recID = 3 THEN Size ELSE NULL END) AS Size3,
MAX(CASE WHEN recID = 4 THEN Size ELSE NULL END) AS Size4,
MAX(CASE WHEN recID = 5 THEN Size ELSE NULL END) AS Size5,
MAX(CASE WHEN recID = 6 THEN Size ELSE NULL END) AS Size6,
MAX(CASE WHEN recID = 7 THEN Size ELSE NULL END) AS Size7,
MAX(CASE WHEN recID = 8 THEN Size ELSE NULL END) AS Size8,
MAX(CASE WHEN recID = 1 THEN Qty ELSE NULL END) AS Qty1,
MAX(CASE WHEN recID = 2 THEN Qty ELSE NULL END) AS SQty2,
MAX(CASE WHEN recID = 3 THEN Qty ELSE NULL END) AS Qty3,
MAX(CASE WHEN recID = 4 THEN Qty ELSE NULL END) AS Qty4,
MAX(CASE WHEN recID = 5 THEN Qty ELSE NULL END) AS Qty5,
MAX(CASE WHEN recID = 6 THEN Qty ELSE NULL END) AS Qty6,
MAX(CASE WHEN recID = 7 THEN Qty ELSE NULL END) AS Qty7,
MAX(CASE WHEN recID = 8 THEN Qty ELSE NULL END) AS Qty8
FROM (
SELECT ColorName,
Size,
Qty,
CASE Size
WHEN 'S' THEN 1
WHEN 'M' THEN 2
WHEN 'L' THEN 3
WHEN 'XL' THEN 4
ELSE 0
END AS recID
FROM @Sample
) AS d
GROUP BY ColorName
ORDER BY ColorName[/code]

E 12°55'05.63"
N 56°04'39.26"

denfer
Starting Member

12 Posts

Posted - 2009-04-27 : 05:17:50

Hello
Search on PIVOT
You may found stuff tat will help you
http://www.mssqltips.com/tip.asp?tip=1019

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 05:21:24

PIVOT works great if all aggregations are of same type.
Also, this particular problem has two pivot values, one for size and one CORRESPONDING for quantity.
It makes the use of PIVOT a little more cumbersome.

E 12°55'05.63"
N 56°04'39.26"

kenlok
Starting Member

16 Posts

Posted - 2009-04-27 : 06:01:09

Thanks for Peso and denfer supports!

But I have question about the Size.
I will not know the Size char is "S, M, L, XL" in every time. May be there will "M, LL, XL..."

Is it any solution to get the Size char auto ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 06:19:04

Yes.

DECLARE	@Sample TABLE
	(
		ColorName VARCHAR(20) NOT NULL,
		Size VARCHAR(2) NOT NULL,
		Qty INT NOT NULL
	)

INSERT	@Sample
SELECT	'Color1', 'S',  30 UNION ALL
SELECT	'Color1', 'M',  38 UNION ALL
SELECT	'Color1', 'L',  39 UNION ALL
SELECT	'Color1', 'XL', 50 UNION ALL
SELECT	'Color2', 'S',  30 UNION ALL
SELECT	'Color2', 'M',  30 UNION ALL
SELECT	'Color2', 'L',  30 UNION ALL
SELECT	'Color2', 'XL', 30

SELECT		ColorName,
		MAX(CASE WHEN recID = 1 THEN Size ELSE NULL END) AS Size1,
		MAX(CASE WHEN recID = 2 THEN Size ELSE NULL END) AS Size2,
		MAX(CASE WHEN recID = 3 THEN Size ELSE NULL END) AS Size3,
		MAX(CASE WHEN recID = 4 THEN Size ELSE NULL END) AS Size4,
		MAX(CASE WHEN recID = 5 THEN Size ELSE NULL END) AS Size5,
		MAX(CASE WHEN recID = 6 THEN Size ELSE NULL END) AS Size6,
		MAX(CASE WHEN recID = 7 THEN Size ELSE NULL END) AS Size7,
		MAX(CASE WHEN recID = 8 THEN Size ELSE NULL END) AS Size8,
		MAX(CASE WHEN recID = 1 THEN Qty ELSE NULL END) AS Qty1,
		MAX(CASE WHEN recID = 2 THEN Qty ELSE NULL END) AS SQty2,
		MAX(CASE WHEN recID = 3 THEN Qty ELSE NULL END) AS Qty3,
		MAX(CASE WHEN recID = 4 THEN Qty ELSE NULL END) AS Qty4,
		MAX(CASE WHEN recID = 5 THEN Qty ELSE NULL END) AS Qty5,
		MAX(CASE WHEN recID = 6 THEN Qty ELSE NULL END) AS Qty6,
		MAX(CASE WHEN recID = 7 THEN Qty ELSE NULL END) AS Qty7,
		MAX(CASE WHEN recID = 8 THEN Qty ELSE NULL END) AS Qty8
FROM		(
			SELECT	ColorName,
				Size,
				Qty,
				ROW_NUMBER() OVER (PARTITION BY ColorName ORDER BY Size) AS recID
			FROM	@Sample
		) AS d
GROUP BY	ColorName
ORDER BY	ColorName

E 12°55'05.63"
N 56°04'39.26"

kenlok
Starting Member

16 Posts

Posted - 2009-04-27 : 21:08:16

Peso, Thanks your support!

The problem is solve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources