SQL Help 1

[cs2009]

Using SQL Server 2005.

I have this data:

[b]Order No    Seq    Comment[/b]
691164       1     This is comment A
691164       2     This is comment B
691164       4     This is comment D
123456       1     Comment X
123456       2     Comment Y
123456       4     Comment Z

What would be the SQL to return it like this?

[b]Order No Comment 1          Comment 2          Comment 3[/b] 
691165   This is comment A  This is comment B  This is comment D
123456   Comment X          Comment Y          Comment Z

I don't need to keep the seq number, but the comments need to be in the proper order. I don't know if a pivot would work in this case because I don't need to sum anything.

 

[bborissov]

-- Preparing test data, yuo don't need this

CREATE TABLE #Test (OrderNo int,  Seq int, Comment varchar(200))

INSERT INTO #Test VALUES(691164, 1, 'This is comment A')
INSERT INTO #Test VALUES(691164, 2, 'This is comment B')
INSERT INTO #Test VALUES(691164, 4, 'This is comment D')

INSERT INTO #Test VALUES(123456, 1, 'Comment X')
INSERT INTO #Test VALUES(123456, 2, 'Comment Y')
INSERT INTO #Test VALUES(123456, 4, 'Comment Z')
--- END


DECLARE @max_no int

SELECT @max_no = MAX(TEST)
FROM (SELECT OrderNo, ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY Seq) AS test
             FROM #Test) Tst

DECLARE @exec varchar(8000), @i int
SET @exec = 'SELECT OrderNo'
SET @i = 0
WHILE @max_no > 0
      BEGIN
          SET @i = @i + 1
          SET @exec = @exec +
                     ', MAX(CASE WHEN Test = '+CAST(@i as varchar(15))+
                     ' THEN Comment END) AS Comment'+CAST(@i as varchar(15))
          SET @max_no = @max_no - 1      
      END
SET @exec = @exec +
            ' FROM (SELECT OrderNo, Comment, ROW_NUMBER() OVER (PARTITION BY OrderNo ORDER BY Seq) AS test'+
            '              FROM #Test) Tst'+
            ' GROUP BY OrderNo'      
      
EXEC (@exec)

--- Drop test table
DROP TABLE #Test

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[cs2009]

Thanks, Boris. You are the best!

 

[bborissov]

You are welcome

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

If the maximum number of comments per OrderNo is fixed and it's 3, then you can use a simple PIVOT query:

CREATE TABLE #Test (OrderNo int,  Seq int, Comment varchar(200))

INSERT INTO #Test VALUES(691164, 1, 'This is comment A')
INSERT INTO #Test VALUES(691164, 2, 'This is comment B')
INSERT INTO #Test VALUES(691164, 4, 'This is comment D')

INSERT INTO #Test VALUES(123456, 1, 'Comment X')
INSERT INTO #Test VALUES(123456, 2, 'Comment Y')
INSERT INTO #Test VALUES(123456, 4, 'Comment Z')

;with cte as (select OrderNo, Comment, 'Comment' + 
cast(row_number() over (partition by OrderNo order by Seq) as varchar(10)) as Row from #Test)

select * from cte PIVOT (max(Comment) for Row IN ([Comment1],[Comment2],[Comment3])) pvt

PluralSight Learning Library

 

[cs2009]

Thanks also, Markros. You to are among the best. Your solution is simple and elegant.