Flat Table - 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)

Flat Table

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-04 : 03:14:21

i have table(TABLE1) that code can return more than one, like:

# Code Sum   status
1  1    100    1
2  2    200    2

i want that will be flat, i mean when code :1=2 then

# Code Sum   status Code Sum   status
1  1   100    100    2   200     2

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-04 : 03:47:05

declare @temp table ( Code int, Sum int, status int)
insert into @temp
select 1 , 100 , 1 union all
select 2 , 200 , 2

select max(case when code = 1 then code end) code,
max(case when code = 1 then sum end) sum,
max(case when code = 1 then status end) status,
max(case when code = 2 then code end) code,
max(case when code = 2 then sum end) sum,
max(case when code = 2 then status end) status from @temp

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-04 : 04:31:02

try this


declare @temp table ( Id Int, Code int, Sum int, status int)
insert into @temp
select 1,1 , 100 , 1 union all
select 1,2 , 200 , 2


SELECT A.Id,A.CODE, A.SUM, A.status, B.CODE, B.SUM, B.status
FROM @Temp A INNER JOIN @Temp B
ON A.Id = B.Id
AND a.Code = 1
AND B.Code = 2

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-03-04 : 07:28:57

the query of Mangal Pardeshi ,not work.

cause i have 10,000 rows in status 1
and 500 in status 2

and i get 500,event if i add LEFT JOIN.but i want to get 10,000 rows.

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-04 : 08:11:28

declare @temp table ( Id Int, Code int, Sum int, status int)
insert into @temp
select 1,1 , 100 , 1 union all
select 1,2 , 200 , 2 union all
select 2,2 , 200 , 2 union all
select 3,1 , 200 , 2

SELECT A.Id,A.CODE, A.SUM, A.status, B.CODE, B.SUM, B.status
FROM @Temp A FULL JOIN @Temp B
ON A.Id = B.Id
AND a.Code = 1
AND B.Code = 2
WHERE A.CODE=1 OR B.CODE=2

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources