Data transformation - 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

SQL Server Development (2000)

Data transformation

Author

Topic

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-12 : 06:23:19

I get the feeling this shouldn't be too hard, but I can't get anywhere with it :(

I wish to have a "flattened" list of cars like so:


ID     Car1     Price1     Car2      Price2     Car3     Price3
1      Ford     10000      Porsche   20000      NULL     NULL
2      NULL     NULL       Porsche   20000      Ferrari  30000
3      Ford     10000      Porsche   20000      Ferrari  30000

Heres the definitions & sample values


CREATE TABLE [dbo].[Cars] (
	[ID] [int] NOT NULL ,
	[Car1] [varchar] (50) NULL ,
	[Price1] [int] NULL ,
	[Car2] [varchar] (50) NULL ,
	[Price2] [int] NULL ,
	[Car3] [varchar] (50) NULL ,
	[Price3] [int] NULL 
) 

insert cars values (1,'Ford','10000',NULL,NULL,NULL,NULL)
insert cars values (1,NULL,NULL,'Porsche','20000',NULL,NULL)
insert cars values (2,NULL,NULL,NULL,NULL,'Ferrari','30000')
insert cars values (2,NULL,NULL,'Porsche','20000',NULL,NULL)
insert cars values (3,'Ford','10000',NULL,NULL,NULL,NULL)
insert cars values (3,NULL,NULL,NULL,NULL,'Ferrari','30000')
insert cars values (3,NULL,NULL,'Porsche','20000',NULL,NULL)

Thanks in advance!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-06-12 : 06:54:22

SELECT [ID],
MAX(Car1) AS Car1, MAX(Price1) AS Price1,
MAX(Car2) AS Car2, MAX(Price2) AS Price2,
MAX(Car3) AS Car3, MAX(Price3) AS Price3
FROM Cars
GROUP BY [ID]

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-12 : 11:34:43

Aha! That'll do nicely, cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources