Compressing Rows - 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)

Compressing Rows

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-01-21 : 06:56:35

I have a table like so

Col1 Col2 Col3 Col4 Col5 Col6
01/05/2007 67 42
01/05/2007 32 32
01/05/2007 47 87
01/06/2007 45 91
01/06/2007 25 34
01/06/2007 472 641
01/07/2007 454 44
01/07/2007 45 44
01/07/2007 1468 24
01/08/2007 78 98
01/08/2007 1425 213
01/08/2007 123 584

and want to convert my table to loko like so

Col1 Col2 Col3 Col4 Col5 Col6
01/05/2007 67 42 32 32 47 87
01/06/2007 45 91 25 34 472 641
01/07/2007 454 44 45 44 1468 24
01/08/2007 78 98 1425 213 123 584

basically i want to compress the rows for each date.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 07:05:57

Why would you like to denormalize the table further?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 07:12:35

[code]-- Prepare sample data
DECLARE @Sample TABLE (ID INT, Level1 INT, Level2 INT, Level3 INT, Level4 INT, Level5 INT, Level6 INT)

INSERT @Sample
SELECT 1, NULL, NULL, 10, 20, NULL, NULL UNION ALL
SELECT 1, 10, 20, NULL, NULL, NULL, NULL UNION ALL
SELECT 1, NULL, 10, NULL, 20, NULL, NULL UNION ALL
SELECT 2, NULL, 11, NULL, 13, NULL, NULL UNION ALL
SELECT 2, NULL, 11, 12, NULL, NULL, NULL UNION ALL
SELECT 2, NULL, NULL, 12, 13, NULL, NULL

-- Show the expected output
SELECT f.ID,
f.[1],
f.[2],
f.[3],
f.[4],
f.[5],
f.[6]
FROM (
SELECT p.ID,
p.Value,
ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY p.Value DESC) AS RecID
FROM @Sample AS s
UNPIVOT (
[Value]
FOR [Level] IN (s.Level1, s.Level2, s.Level3, s.Level4, s.Level5, s.Level6)
) AS p
) AS y
PIVOT (
MAX(y.Value)
FOR y.RecID IN ([1], [2], [3], [4], [5], [6])
) AS f
ORDER BY f.ID[/code]

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

jtwork
Yak Posting Veteran

82 Posts

Posted - 2008-01-21 : 07:48:59

ah it squashed up the nulls. This might make more sense.

From this

-----------Col1 Col2 Col3 Col4 Col5 Col6
01/05/2007 67 42 null null null null
01/05/2007 null null 32 32 null null
01/05/2007 null null null null 47 87
01/06/2007 45 91 null null null null
01/06/2007 null null 25 34 null null
01/06/2007 null null null null 472 641
01/07/2007 454 44 null null null null
01/07/2007 null null 45 44 null null
01/07/2007 null null null null 1468 24
01/08/2007 78 98 null null null null
01/08/2007 null null 1425 213 null null
01/08/2007 null null null null 123 584

to this

----------Col1 Col2 Col3 Col4 Col5 Col6
01/05/2007 67 42 32 32 47 87
01/06/2007 45 91 25 34 472 641
01/07/2007 454 44 45 44 1468 24
01/08/2007 78 98 1425 213 123 584

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-21 : 07:54:56

SELECT dt, MAX(Col1), MAX(Col2), MAX(Col3), MAX(Col4), MAX(Col5), MAX(Col6)
FROM Table1
GROUP BY dt

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