Select multiple rows into 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)

Select multiple rows into one row

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-23 : 06:42:20

I have a table(DiaryEntries) that hold the following fields:
Id, EmpId, Date, message.

Each EmpId can have more than one entry in the table
e.g.

1, 123, 20/09/2001, Test
1, 123, 21/09/2001, Test2
1, 123, 21/09/2001, Test3

What I want to do is write a select statement that will write these out for each empId in one line

Select empId as EmpNumber, Date as Date1, Message as Message1, Date as Date2, Message as Message2 .....etc

e.g.
123, 20/09/2001, Test, 21/09/2001, Test2, 21/09/2001, Test3

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 07:05:16

There are a few different ways to do this - for example, see this page: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

You can implement the XML method described on that page for your problem like this:


CREATE TABLE #tmp (EmpId INT, dt VARCHAR(255), testname VARCHAR(255));
INSERT INTO #tmp VALUES 
   ( 123,'20/09/2001', 'Test1'),
   ( 123,'21/09/2001', 'Test2'),
   ( 123,'21/09/2001', 'Test3')

SELECT DISTINCT
   EmpId,
   concatStr
FROM
   #tmp t1
   CROSS APPLY
   (
      SELECT STUFF(
      (SELECT
          ',' AS [text()],
         CAST(dt AS VARCHAR(32)) AS [text()],
         ',' AS [text()],
         CAST(testname AS VARCHAR(32)) AS [text()]
      FROM
         #tmp t2
      WHERE
         t2.EmpId = t1.EmpId
      FOR XML PATH(''),TYPE ).value('.','VARCHAR(255)'),1,1,'')
   ) t2(concatStr);

DROP TABLE #tmp;

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-23 : 08:29:10

Thats great, now if i wanted to only select the top 10 entries but still output the comma separted spaces for each how would I do this. As not every row will always have 10 entries but I want all rows to come out with the same comma separated length

e.g

123, 20/09/2001, Test, 21/09/2001, Test2, 21/09/2001, Test3, 21/09/2001, Test4, 21/09/2001, Test5, 21/09/2001, Test6, 21/09/2001,
Test7, 21/09/2001, Test8, 21/09/2001, Test9, 21/09/2001, Test10, 21/09/2001,

456, 20/09/2001, Test, 21/09/2001, Test2, 21/09/2001, Test3, 21/09/2001, Test4, 21/09/2001, Test5, 21/09/2001,,,,,,,,,,,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 08:45:09

Database purists will really would look down upon you and on me for trying to do this type of thing in the database. And for some good reasons - in general, formatting such as what we are trying to do here is best done on the client side e.g. in the user-interface code. Nonetheless, here is one way you can do it.


...
SELECT DISTINCT
   EmpId,
   concatStr + ISNULL( REPLICATE(',',10-(LEN(concatStr)-LEN(REPLACE(concatStr,',','')))),'')
...

Replace the red 10 with however many TOTAL commas you want to have in the final result.

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-23 : 09:44:44

thanks

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-23 : 15:54:35

FOR XML PATH(''),TYPE ).value('.','VARCHAR(255)'),1,1,'')

does the varchar(255) part of the following limit the row to this number of characters as I need my row to be longer.

FOR XML PATH(''),TYPE ).value('.','VARCHAR(255)'),1,1,'')

For example the Date will be varchar(8) and the message will be varchar(60) and each row can have 10 of each.

Also I want the top 10 for each empid that might be in the table, at the moment it is giving me just 10 records

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 16:12:58

Yes, it does. Sorry about that. Use


FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)'),1,1,'')

Also, if your testname would be longer than 32, you may want to change the part that reads:

CAST(testname AS VARCHAR(32))

Looper
Yak Posting Veteran

68 Posts

Posted - 2011-06-23 : 16:45:31

I have tried that but it seems to only allow 256 chars. Here is my current code


BEGIN


CREATE TABLE #tmp (PayNo VARCHAR(8), [Date] VARCHAR(8), [Message] VARCHAR(60)); 
INSERT INTO #tmp 
SELECT  
                ete.PayNo As PayNo, 
                ISNULL(CONVERT(VARCHAR(8),de.Date,112),'') As Date, 
                CASE 
                WHEN Len(ltrim(rtrim(ISNULL(REPLACE(REPLACE(de.Message, CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), '')))) > 60

                        THEN Substring(ltrim(rtrim(ISNULL(REPLACE(REPLACE(de.Message, CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), ''))),1,55) + '.....'

                ELSE ltrim(rtrim(ISNULL(REPLACE(REPLACE(de.Message, CHAR(13), CHAR(32)), CHAR(10), CHAR(32)), ''))) 
                END As [Message] 
FROM Pay.dbo.EmpExtract ete 
INNER JOIN [Pay].[dbo].[Diary] de ON de.EmploymentId = ete.Id 
WHERE  (de.ActionedById IS NULL) 
ORDER BY de.Date DESC

SELECT DISTINCT 
   PayNo As PayNo, 
   concatStr + ISNULL( REPLICATE(',',20-(LEN(concatStr)-LEN(REPLACE(concatStr,',','')))),'') As DiaryEntries 
FROM 
   #tmp t1 
   CROSS APPLY 
   ( 
      SELECT STUFF( 
      (SELECT 
          ',' AS [text()], 
         CAST([Date] AS VARCHAR(8)) AS [text()], 
         ',' AS [text()], 
         CAST([Message] AS VARCHAR(60)) AS [text()] 
      FROM 
         #tmp t2 
      WHERE 
         t2.PayNo = t1.PayNo 
      FOR XML PATH(''),TYPE ).value('.','VARCHAR(max)'),1,1,'') 
   ) t2(concatStr)


drop table #tmp


END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-23 : 18:06:36

This may be an artifact of SQL Server Management Studio. In SSMS, Tools->Options -> Query Results -> SQL Server -> Results To Grid, increase the Maximum Characters Retrieved to a larger number.

If that is not the problem, just for testing purposes, change


concatStr + ISNULL( REPLICATE(',',20-(LEN(concatStr)-LEN(REPLACE(concatStr,',','')))),'') As DiaryEntries

LEN( concatStr + ISNULL( REPLICATE(',',20-(LEN(concatStr)-LEN(REPLACE(concatStr,',','')))),'') ) As DiaryEntries

which will let you see the length of the result string

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources