Multiple Rows into Single Row

Last Post 13 May 2008 04:38 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
wrathyimp
New Member
New Member

--
12 May 2008 11:26 PM
Hi,

I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time Header
10:15:34 AM News1
10:15:34 AM News1
10:15:34 AM News1
11:19:39 AM News2
11:19:39 AM News2
12:35:04 PM News3
12:35:04 PM News3

NewsDetails
Time Text RowC
10:15:34 AM ABC 1
10:15:34 AM DEFG 2
10:15:34 AM HIJKL 3
11:19:39 AM AABB 1
11:19:39 AM CCDD 2
12:35:04 PM ZZYY 1
12:35:04 PM XXWW 2

Required Output
Time Header Text
10:15:34 AM News1 ABCDEFGHIJKL
11:19:39 AM News2 AABBCCDD
12:35:04 PM News3 ZZYYXXWW

Thank you.
SQLUSA
New Member
New Member

--
13 May 2008 02:26 AM
You can use this sample from Northwind:

USE Northwind;

SELECT CategoryId, ProductList=
MAX( CASE SeqNo WHEN 1 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE SeqNo WHEN 2 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE SeqNo WHEN 3 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE SeqNo WHEN 4 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE SeqNo WHEN 5 THEN ProductName ELSE '' END ) + ', '+
MAX( CASE SeqNo WHEN 6 THEN ProductName ELSE '' END )
FROM ( SELECT p1.CategoryId, p1.ProductName, Items=
( SELECT COUNT(*)
FROM Products p2
WHERE p2.CategoryId = p1.CategoryId
AND p2.ProductName <= p1.ProductName )
FROM Products p1 ) D
( CategoryId, ProductName, SeqNo )
GROUP BY CategoryId ;


to build your query.

Note that the list is hard-wired for max 6 items. In SQL Server 2005, you can use XML PATH to eliminate hard-wiring.

In SQL Server 2000 you can use dynamic SQL to eliminate hard-wiring.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server 2005 Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/
wrathyimp
New Member
New Member

--
13 May 2008 03:36 AM
This is the converted code with my tables:
SELECT Header, Details=
MAX( CASE TRow WHEN 1 THEN Text ELSE '' END ) + ', '+
MAX( CASE TRow WHEN 2 THEN Text ELSE '' END ) + ', '+
MAX( CASE TRow WHEN 3 THEN Text ELSE '' END ) + ', '+
MAX( CASE TRow WHEN 4 THEN Text ELSE '' END ) + ', '+
MAX( CASE TRow WHEN 5 THEN Text ELSE '' END ) + ', '+
MAX( CASE TRow WHEN 6 THEN Text ELSE '' END )
FROM ( SELECT p1.Header, p1.Text, Items=
( SELECT COUNT(*)
FROM NewsView p2
WHERE p2.Header = p1.Header
AND p2.x <= p1.x )
FROM NewsView p1 ) D
( Header, Text, TRow)
GROUP BY Header ;

Its not working fine, as the output
Time Header Text
10:15:34 AM News1 ,,,,,ABC
10:15:34 AM News1 ,,,,,ABC
11:19:39 AM News2 ,,,,,AABB
11:19:39 AM News2 ,,,,,CCDD
SQLUSA
New Member
New Member

--
13 May 2008 04:38 AM
I don't think you want the commas. Also include time in the group by:

SELECT Header, Details=
MAX( CASE TRow WHEN 1 THEN Text ELSE '' END ) +
MAX( CASE TRow WHEN 2 THEN Text ELSE '' END ) +
MAX( CASE TRow WHEN 3 THEN Text ELSE '' END ) +
MAX( CASE TRow WHEN 4 THEN Text ELSE '' END ) +
MAX( CASE TRow WHEN 5 THEN Text ELSE '' END ) +
MAX( CASE TRow WHEN 6 THEN Text ELSE '' END )
FROM ( SELECT p1.Header, p1.Text, Items=
( SELECT COUNT(*)
FROM NewsView p2
WHERE p2.Header = p1.Header
AND p2.x <= p1.x )
FROM NewsView p1 ) D
( Header, Text, TRow)
GROUP BY [Time], Header ;


You are not authorized to post a reply.

Acceptable Use Policy