Saturday, 18 August 2012

0 Concatenate row values T-SQL

DECLARE @Reviews TABLE(
        ReviewID INT,
        ReviewDate DATETIME)
DECLARE @Reviewers TABLE(
        ReviewerID   INT,
        ReviewID   INT,
        UserID INT)
DECLARE @Users TABLE(
        UserID  INT,
        FName  VARCHAR(50),
        LName VARCHAR(50)
)
INSERT INTO @Reviews SELECT 1, '12 Jan 2009'
INSERT INTO @Reviews SELECT 2, '25 Jan 2009'
INSERT INTO @Users SELECT 1, 'Bob', ''
INSERT INTO @Users SELECT 2, 'Joe', ''
INSERT INTO @Users SELECT 3, 'Frank', ''
INSERT INTO @Users SELECT 4, 'Sue', ''
INSERT INTO @Users SELECT 5, 'Alice', ''
INSERT INTO @Reviewers SELECT 1, 1, 1
INSERT INTO @Reviewers SELECT 2, 1, 2
INSERT INTO @Reviewers SELECT 3, 1, 3
INSERT INTO @Reviewers SELECT 4, 2, 4
INSERT INTO @Reviewers SELECT 5, 2, 5
SELECT  *,
        ( 
                SELECT  u.FName + ','
                FROM    @Users u INNER JOIN     
                                @Reviewers rs ON u.UserID = rs.UserID
                WHERE   rs.ReviewID = r.ReviewID
                FOR XML PATH('')
        ) AS ProductsFROM    @Reviews r

0 comments:

Post a Comment

 

My MSSQL Search Collections Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates