Combine multiple records into one

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)

Combine multiple records into one

Author

Topic

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-05-12 : 08:24:49

Hi friends,

I am struggling with a problem. In order to create reports out of an event logging, I need to convert this table:

ID Recipe Description
1 Ingredient Add water to milk
1 Ingredient 1L water
1 Ingredient 2L milk
1 Measure 0.98L water
1 Measure 2.10L milk

Into this query:

Batch__________________________Measure1__Measure2
Add water to milk, 1L water, 2L milk 0.98L water 2.10L milk

A step between this two tables could be a sort of 'transponation' where the table is converted into 5 colums and 1 row in stead of 1 column and five rows, per ID.

But the number of ingredients and measurements varies, and the ID value combines the right measurement with the right ingredients.

Anyone got help please?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-12 : 08:43:46

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

djorre
Yak Posting Veteran

94 Posts

Posted - 2009-05-12 : 09:31:32

Thank you very much!

But it sets everything into just one column, comma sepereted. I need the recipes into one and the measurements into seperate colums. And because that code is pretty high-level to me, I cannot edit it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-12 : 09:52:12

[code]SELECT m.ID,m.Batch,
MAX(CASE WHEN n.Seq=1 THEN n.Description ELSE NULL END) AS Measure1,
MAX(CASE WHEN n.Seq=2 THEN n.Description ELSE NULL END) AS Measure2,
MAX(CASE WHEN n.Seq=3 THEN n.Description ELSE NULL END) AS Measure3,
...
FROM
(
SELECT t.ID,LEFT(bl.BatchList,LEN(bl.BatchList)-1) AS Batch
FROM (SELECT DISTINCT ID FROM YourTable) t
CROSS APPLY(SELECT Description + ','
FROM YourTable
WHERE ID=t.ID
FOR XML PATH(''))bl(BatchList)
)m
CROSS APPLY (SELECT ROW_NUMBER() OVER (ORDER BY Description) AS Seq,
Description
FROM YourTable
WHERE ID=m.ID
AND Recipe='Measure')n
GROUP BY m.ID,m.Batch
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources