How combine values of multiple rows in 1 row ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

How combine values of multiple rows in 1 row ?

Author

Topic

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 12:30:29

I need to have values that come from different rows combined in 1 single row.


select
id,
case when b.Item='Loan #' then ItemData  end 'Loan',
case when b.Item='Reason Code') then ItemData  end 'Reason'
from Items a
inner join ItemDef b on b.ItemDefID = a.ItemDefID

result
id********loan#*********reason
1---------100
2---------------------------faulty

desired result

id********loan#********reason
1---------100---------------faulty

is this possible ?

robert

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-12 : 12:44:51

Something like this...?

select
  id,
  max(case when b.Item='Loan #' then ItemData end) 'Loan',
  max(case when b.Item='Reason Code' then ItemData end) 'Reason'
from Items a
  inner join ItemDef b on b.ItemDefID = a.ItemDefID
group by id

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 12:52:57

Nope, it shows the same. The rows are all different. Do I have to use cursors for this ?

robert

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 12:55:20

can you post your table structure, some more sample data and the desired result ?

KH

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 13:09:01

Items
ID...........int
ItemDefID....int
ItemData.....varchar(20)

ItemDef
ItemDefID....int
ItemName.....varchar(20)

Items-----------------------------------ItemDef
ID--------ItemDefID-----ItemData-------ItemDefID-------ItemName
1............100-----------faulty----------100................Loan
2............101-----------x42------------101................Batch
3............102-----------4-2-2006------103................Date

would like to see
-----loan--------batch--------date
1.....faulty...........x42...........4-2006

robert

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-12 : 13:19:44

try this. ..

select	(ID / 4) + 1,
	max(case when ItemDefID = 100 then ItemData end) as [loan],
	max(case when ItemDefID = 101 then ItemData end) as [batch],
	max(case when ItemDefID = 102 then ItemData end) as [date]
from	Items
group by (ID / 4) + 1

KH

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 13:25:41

That is amazing.
It works!
Thanks.

robert

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 13:58:35

this works even better:


select	(ID / ID),
	max(case when ItemDefID = 100 then ItemData end) as [loan],
	max(case when ItemDefID = 101 then ItemData end) as [batch],
	max(case when ItemDefID = 102 then ItemData end) as [date]
from	Items
group by (ID / ID)

robert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-12 : 14:58:58

quote:

Originally posted by eurob

this works even better:


select	(ID / ID),
	max(case when ItemDefID = 100 then ItemData end) as [loan],
	max(case when ItemDefID = 101 then ItemData end) as [batch],
	max(case when ItemDefID = 102 then ItemData end) as [date]
from	Items
group by (ID / ID)

robert

Why not this?

select	ID,
	max(case when ItemDefID = 100 then ItemData end) as [loan],
	max(case when ItemDefID = 101 then ItemData end) as [batch],
	max(case when ItemDefID = 102 then ItemData end) as [date]
from	Items
where   id = 1
group by ID

Peter Larsson
Helsingborg, Sweden

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 15:23:51

I can't use a [where] clause because it's for a report showing all records, otherwise that would work indeed.

robert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-12 : 16:43:53

Just remove the WHERE then?

select	ID,
	max(case when ItemDefID = 100 then ItemData end) as [loan],
	max(case when ItemDefID = 101 then ItemData end) as [batch],
	max(case when ItemDefID = 102 then ItemData end) as [date]
from	Items
group by ID

I can't imagine your SELECT (id / id) would be useful, since the result is always 1. Khtan's code should produce the right output.

Peter Larsson
Helsingborg, Sweden

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-12 : 22:56:55

It doesn't because there are id's that that are larger than the count.
e.g
ID 1,5,200 is a count of 3 which would result in extra rows when not dividing by the id itself.
Also

quote:

select ID,
max(case when ItemDefID = 100 then ItemData end) as [loan],
max(case when ItemDefID = 101 then ItemData end) as [batch],
max(case when ItemDefID = 102 then ItemData end) as [date]
from Items
group by ID

doesn't give one row, but 3. The rowids are all different so grouping on just the ID wouldn't work. Please try it.

robert

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-13 : 00:14:09

I was assuming your ID is a running no and also group ID = 1, 2 & 3 as one record and 4, 5 & 6 as another.

Will you have more than 3 records in Items table ?

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-13 : 06:11:40

I think he is talking about there are gaps in the id sequence... But still need every three consecutive records into one row.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-13 : 07:12:09

quote:
Originally posted by Peso

I think he is talking about there are gaps in the id sequence... But still need every three consecutive records into one row.

I'm not convinced, but if that's the case, this might work...

--data
declare @Items table (ID int, ItemDefID int, ItemData varchar(20))
insert @Items
          select 1, 100, 'faulty'
union all select 2, 101, 'x42'
union all select 3, 102, '4-2-2006'
union all select 4, 100, 'a'
union all select 6, 101, 'b'
union all select 9, 102, 'c'
union all select 34, 100, 'd'
union all select 223, 101, 'e'
union all select 3243, 102, 'f'

--calculation
select
    GroupId,
    min(id) as Id,
	max(case when ItemDefID = 100 then ItemData end) as [loan],
	max(case when ItemDefID = 101 then ItemData end) as [batch],
	max(case when ItemDefID = 102 then ItemData end) as [date]
from (
    select *, (select count(*) from @Items where id < a.id) / 3 as GroupId from @Items a) b
group by
    GroupId

/*results
GroupId     Id          loan                 batch                date                 
----------- ----------- -------------------- -------------------- -------------------- 
0           1           faulty               x42                  4-2-2006
1           4           a                    b                    c
2           34          d                    e                    f
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

eurob
Posting Yak Master

100 Posts

Posted - 2006-07-13 : 15:22:56

Khtan, yes I have thousands of records in that table. I will still go with your first solution, it's simple and works great.
Thanks all.

robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources