cross tab....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 2000 Forums

Transact-SQL (2000)

cross tab....help.... :((

Author

Topic

mangyun
Starting Member

8 Posts

Posted - 2006-09-19 : 08:16:46

Please help, need to make these table turn into
____________________
code type qty1 qty2 qty3
ABCDEFG 0 1
ABCDEFG 1 2
ABGO 0 1
ABFTER 2 1
ABFTER 3 1
____________________
this below :

code type qty1 qty2 qty3 type1 qty11 qty12 qty13
ABCDEFG 0 1 1 2
ABGO 0 1
ABFTER 2 1 3 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:23:15

This will give you an idea of how to start

SELECT [Code Type],
SUM(qty1) Qty1,
SUM(qty2) Qty2,
SUM(qty3) Qty3
FROM Table
GROUP BY [Code Type]

Peter Larsson
Helsingborg, Sweden

mangyun
Starting Member

8 Posts

Posted - 2006-09-19 : 08:33:16

Help, cross tab
please see pic in link
http://www.surfer-girl.com/users/mangyun/question.JPG
thanks

mangyun

mangyun
Starting Member

8 Posts

Posted - 2006-09-19 : 08:37:34

Thanks peter for the quick response, but that is not what i meant,
please see my link herehttp://www.surfer-girl.com/users/mangyun/question.JPG

Mangyun, Bali

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 08:44:21

[code]-- Prepare test data
DECLARE @Source TABLE (Code VARCHAR(10), Type INT, qty1 INT, qty2 INT, qty3 INT)

INSERT @Source
SELECT 'ABCDEFG', 0, NULL, NULL, 1 UNION ALL
SELECT 'ABCDEFG', 1, 2, NULL, NULL UNION ALL
SELECT 'ABGO', 0, NULL, 1, NULL UNION ALL
SELECT 'ABFTER', 2, NULL, NULL, 1 UNION ALL
SELECT 'ABFTER', 3, 2, NULL, NULL

select * from @source

-- Do the work
SELECT lc.Code,
lc.Type,
lc.qty1,
lc.qty2,
lc.qty3,
CASE WHEN rc.Type = lc.Type THEN NULL ELSE rc.Type END Type1,
CASE WHEN rc.Type = lc.Type THEN NULL ELSE rc.Qty1 END Qty11,
CASE WHEN rc.Type = lc.Type THEN NULL ELSE rc.Qty2 END Qty12,
CASE WHEN rc.Type = lc.Type THEN NULL ELSE rc.Qty3 END Qty13
FROM (
SELECT s.*
FROM (
SELECT Code,
MIN(Type) Type
FROM @Source
GROUP BY Code
) x
INNER JOIN @Source s ON s.Code = x.Code AND s.Type = x.Type
) lc
INNER JOIN (
SELECT s.*
FROM (
SELECT Code,
MAX(Type) Type
FROM @Source
GROUP BY Code
) x
INNER JOIN @Source s ON s.Code = x.Code AND s.Type = x.Type
) rc ON rc.Code = lc.Code
ORDER BY lc.Code,
rc.Code[/code]
Peter Larsson
Helsingborg, Sweden

mangyun
Starting Member

8 Posts

Posted - 2006-09-19 : 23:22:33

Awesome, thanks peter. Works like a charm.
Just curious, do you have any idea on how to make that more dynamic, for example if one(1) code has more than two(2) type....

Mangyun

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources