Consolodating rows into extra 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

SQL Server 2005 Forums

Transact-SQL (2005)

Consolodating rows into extra columns

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-03-12 : 07:47:11

Consider this table

Make Model Derivative Term Miles Maint CH PCH
AUDI A4 TDI 2 10000 1 100 110
AUDI A4 TDI 2 10000 0 200 210
AUDI A4 TDI 2 20000 1 300 310
AUDI A4 TDI 2 20000 0 400 410
AUDI A4 TDI 2 30000 1 500 510
AUDI A4 TDI 2 30000 0 600 610
AUDI A4 TDI 3 10000 1 700 710
AUDI A4 TDI 3 10000 0 800 810
AUDI A4 TDI 3 20000 1 900 910
AUDI A4 TDI 3 20000 0 950 955
AUDI A4 TDI 3 30000 1 975 980
AUDI A4 TDI 3 30000 0 985 990

What I'm hoping to do is consolodate these 12 rows into 1 row with the following fields

Make
Model
Derivative
2Year10kMaintainedCH
2Year10kNotMaintainedCH
2Year20kMaintainedCH
2Year20kNotMaintainedCH
2Year30kMaintainedCH
2Year30kNotMaintainedCH
3Year10kMaintainedCH
3Year10kNotMaintainedCH
3Year20kMaintainedCH
3Year20kNotMaintainedCH
3Year30kMaintainedCH
3Year30kNotMaintainedCH
2Year10kMaintainedPCH
2Year10kNotMaintainedPCH
2Year20kMaintainedPCH
2Year20kNotMaintainedPCH
2Year30kMaintainedPCH
2Year30kNotMaintainedPCH
3Year10kMaintainedPCH
3Year10kNotMaintainedPCH
3Year20kMaintainedPCH
3Year20kNotMaintainedPCH
3Year30kMaintainedPCH
3Year30kNotMaintainedPCH

Is there any way to do this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 07:54:52

Yes, but why would you denormalize a table?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:01:07

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
Make VARCHAR(4),
Model VARCHAR(2),
Derivative VARCHAR(3),
Term TINYINT,
Miles SMALLINT,
Maint TINYINT,
CH SMALLINT,
PCH SMALLINT
)

INSERT @Sample
SELECT 'AUDI', 'A4', 'TDI', 2, 10000, 1, 100, 110 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 2, 10000, 0, 200, 210 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 2, 20000, 1, 300, 310 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 2, 20000, 0, 400, 410 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 2, 30000, 1, 500, 510 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 2, 30000, 0, 600, 610 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 3, 10000, 1, 700, 710 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 3, 10000, 0, 800, 810 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 3, 20000, 1, 900, 910 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 3, 20000, 0, 950, 955 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 3, 30000, 1, 975, 980 UNION ALL
SELECT 'AUDI', 'A4', 'TDI', 3, 30000, 0, 985, 990

-- Solution
SELECT Make,
Model,
Derivative,
MAX(CASE WHEN Term = 2 AND Miles = 10000 AND Maint = 1 THEN CH ELSE NULL END) AS [2Year10kMaintainedCH],
MAX(CASE WHEN Term = 2 AND Miles = 10000 AND Maint = 0 THEN CH ELSE NULL END) AS [2Year10kNotMaintainedCH],
MAX(CASE WHEN Term = 2 AND Miles = 20000 AND Maint = 1 THEN CH ELSE NULL END) AS [2Year20kMaintainedCH],
MAX(CASE WHEN Term = 2 AND Miles = 20000 AND Maint = 0 THEN CH ELSE NULL END) AS [2Year20kNotMaintainedCH],
MAX(CASE WHEN Term = 2 AND Miles = 30000 AND Maint = 1 THEN CH ELSE NULL END) AS [2Year30kMaintainedCH],
MAX(CASE WHEN Term = 2 AND Miles = 30000 AND Maint = 0 THEN CH ELSE NULL END) AS [2Year30kNotMaintainedCH],
MAX(CASE WHEN Term = 3 AND Miles = 10000 AND Maint = 1 THEN CH ELSE NULL END) AS [3Year10kMaintainedCH],
MAX(CASE WHEN Term = 3 AND Miles = 10000 AND Maint = 0 THEN CH ELSE NULL END) AS [3Year10kNotMaintainedCH],
MAX(CASE WHEN Term = 3 AND Miles = 20000 AND Maint = 1 THEN CH ELSE NULL END) AS [3Year20kMaintainedCH],
MAX(CASE WHEN Term = 3 AND Miles = 20000 AND Maint = 0 THEN CH ELSE NULL END) AS [3Year20kNotMaintainedCH],
MAX(CASE WHEN Term = 3 AND Miles = 30000 AND Maint = 1 THEN CH ELSE NULL END) AS [3Year30kMaintainedCH],
MAX(CASE WHEN Term = 3 AND Miles = 30000 AND Maint = 0 THEN CH ELSE NULL END) AS [3Year30kNotMaintainedCH],
MAX(CASE WHEN Term = 2 AND Miles = 10000 AND Maint = 1 THEN CH ELSE NULL END) AS [2Year10kMaintainedPCH],
MAX(CASE WHEN Term = 2 AND Miles = 10000 AND Maint = 0 THEN PCH ELSE NULL END) AS [2Year10kNotMaintainedPCH],
MAX(CASE WHEN Term = 2 AND Miles = 20000 AND Maint = 1 THEN PCH ELSE NULL END) AS [2Year20kMaintainedPCH],
MAX(CASE WHEN Term = 2 AND Miles = 20000 AND Maint = 0 THEN PCH ELSE NULL END) AS [2Year20kNotMaintainedPCH],
MAX(CASE WHEN Term = 2 AND Miles = 30000 AND Maint = 1 THEN PCH ELSE NULL END) AS [2Year30kMaintainedPCH],
MAX(CASE WHEN Term = 2 AND Miles = 30000 AND Maint = 0 THEN PCH ELSE NULL END) AS [2Year30kNotMaintainedPCH],
MAX(CASE WHEN Term = 3 AND Miles = 10000 AND Maint = 1 THEN PCH ELSE NULL END) AS [3Year10kMaintainedPCH],
MAX(CASE WHEN Term = 3 AND Miles = 10000 AND Maint = 0 THEN PCH ELSE NULL END) AS [3Year10kNotMaintainedPCH],
MAX(CASE WHEN Term = 3 AND Miles = 20000 AND Maint = 1 THEN PCH ELSE NULL END) AS [3Year20kMaintainedPCH],
MAX(CASE WHEN Term = 3 AND Miles = 20000 AND Maint = 0 THEN PCH ELSE NULL END) AS [3Year20kNotMaintainedPCH],
MAX(CASE WHEN Term = 3 AND Miles = 30000 AND Maint = 1 THEN PCH ELSE NULL END) AS [3Year30kMaintainedPCH],
MAX(CASE WHEN Term = 3 AND Miles = 30000 AND Maint = 0 THEN PCH ELSE NULL END) AS [3Year30kNotMaintainedPCH]
FROM @Sample
GROUP BY Make,
Model,
Derivative
ORDER BY Make,
Model,
Derivative[/code]

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

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-03-12 : 08:02:53

I need to get it all into one row the output each row as an XML item, i'd rather do this processing server side if possible.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:11:54

SQL Server 2005 has extened XML support!
I believe you can get your output directly from table with FOR XML operators.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources