rows belonging to particular code in one row

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)

rows belonging to particular code in one row

Author

Topic

dhroove
Starting Member

1 Post

Posted - 2008-05-29 : 15:18:47

hi...

I have a query which displays record in following way

Code | Amt
-----------------------
101 | 9000
101 | 3000
101 | 4000
101 | 2000
101 | 8000
102 | 1000
102 | 2000
102 | 4000
102 | 3500
102 | 1100
103 | 6000
103 | 5000
103 | 1000
103 | 1100
103 | 2900

I want to display all rows belonging to particular code in one row
as follows

Code | Amt1 | Amt2 | Amt3 | Amt4 | Amt5
---------------------------------------------------
101 | 9000 | 3000 | 4000 | 2000 | 8000
102 | 1000 | 2000 | 4000 | 3500 | 1100
103 | 6000 | 5000 | 1000 | 1100 | 2900

Can someone please help me out? Waiting for a eager reply

VD

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-29 : 15:44:05

This reply is not too eager but just a few questions first :)

Are there always 5 different Amt values per Code?
Are there any other columns that define the sequence? (or can it be random that 9000 is Amt1 rather than Amt2)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-29 : 15:57:32

Perhaps you can work out the details on your own based on this sample code.
This will take the first Amts for each Code in order of Amt desc and assign them the Amt1-Amt5 columns:


declare @t table (Code int, Amt int)
insert @t
select 101, 9000 union all
select 101, 3000 union all
select 101, 4000 union all
select 101, 2000 union all
select 101, 8000 union all
select 102, 1000 union all
select 102, 2000 union all
select 102, 4000 union all
select 102, 3500 union all
select 102, 1100 union all
select 103, 6000 union all
select 103, 5000 union all
select 103, 1000 union all
select 103, 1100 union all
select 103, 2900

select code
       ,amt1 = max(case when rn = 1 then amt end)
       ,amt2 = max(case when rn = 2 then amt end)
       ,amt3 = max(case when rn = 3 then amt end)
       ,amt4 = max(case when rn = 4 then amt end)
       ,amt5 = max(case when rn = 5 then amt end)
from   (select Code, Amt, row_number() over (partition by code order by Amt desc) rn from   @t) d
where  rn < 6
group by code

output:
code        amt1        amt2        amt3        amt4        amt5
----------- ----------- ----------- ----------- ----------- -----------
101         9000        8000        4000        3000        2000
102         4000        3500        2000        1100        1000
103         6000        5000        2900        1100        1000

Be One with the Optimizer
TG

raja_saminathan
Starting Member

12 Posts

Posted - 2008-05-30 : 10:09:33

Hi,

There is a slight Difference in the TG's Output from the Given Output due to order by Desc ,so kindly make the Changes as per your Requirement.

Rajesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources