1

I have two tables, tempUsers and tempItems. These two tables have a one to many relationship.

When I use an inner join on these two tables the result looks like this:

user  | Category | Date
_______________________
Jack  | Shoes    | 01/01/2011
Jack  | Tie      |02/01/2011
Jack  | Glass    |03/03/2011
Peggy | Shoe     | 02/02/2012
Peggy | Skirt    | 02/12/2013

I would instead like a result that looks like this:

User | Category1  | Category2 | Category3 | Dates
-------------------------------------------------
Jack   | Shoes      | Tie       | Glass     | 01/01/2011,02/01/2011,03/03/2011
Peggy  | Shoe       | Skirt     | ....      | 02/02/2012,02/12/2013

Thank you

WorkInProgress
  • 393
  • 6
  • 16
  • Can there be more than 3 categories? – sgeddes May 28 '13 at 18:29
  • Yes, there can be more than 3 categories. I have alfready figured out that part. Here is the link for that http://stackoverflow.com/questions/16737552/sql-server-2008-row-to-column/16738652#16738652 Now I want to display another field date in comma separated values – WorkInProgress May 28 '13 at 18:45

1 Answers1

3

Try this one -

Query:

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
        [user] VARCHAR(10)
      , Category VARCHAR(10)
      , [Date] DATETIME
)

INSERT INTO #temp ([user], Category, [Date])
VALUES 
    ('Jack',  'Shoes', '20110101'),
    ('Jack',  'Tie',   '20110102'),
    ('Jack',  'Glass', '20110303'),
    ('Peggy', 'Shoe',  '20120202'),
    ('Peggy', 'Skirt', '20131202')

DECLARE @Columns NVARCHAR(MAX)

SELECT @Columns = STUFF((
    SELECT DISTINCT
        ',[' + 'Category' + CAST(
        ROW_NUMBER() OVER (PARTITION BY t.[user] ORDER BY (SELECT 1)) AS VARCHAR(3)) + ']'
    FROM #temp t
    FOR XML PATH (''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT [user], ' + @Columns + ', Dates 
FROM (
    SELECT 
          t.[user]
        , t.category
        , rn = ''Category'' + CAST(ROW_NUMBER() OVER (PARTITION BY t.[user] ORDER BY (SELECT 1)) AS VARCHAR(3))
        , Dates = STUFF((
              SELECT '', '' + CONVERT(VARCHAR(10), t2.[Date], 103)
              FROM #temp t2
              WHERE t2.[user] = t.[user]
              FOR XML PATH(''''), TYPE).value(''.'', ''VARCHAR(MAX)''), 1, 2, '''')  
    FROM #temp t
) t3 
PIVOT (
    MAX(category) 
    FOR rn IN (' + @Columns + ')
) p'

PRINT @SQL

EXECUTE sys.sp_executesql @SQL

Output:

SELECT [user], [Category1],[Category2],[Category3], Dates 
FROM (
    SELECT 
          t.[user]
        , t.category
        , rn = 'Category' + CAST(ROW_NUMBER() OVER (PARTITION BY t.[user] ORDER BY (SELECT 1)) AS VARCHAR(3))
        , Dates = STUFF((
              SELECT ', ' + CONVERT(VARCHAR(10), t2.[Date], 103)
              FROM #temp t2
              WHERE t2.[user] = t.[user]
              FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')  
    FROM #temp t
) t3 
PIVOT (
    MAX(category) 
    FOR rn IN ([Category1],[Category2],[Category3])
) p

Results:

user       Category1  Category2  Category3  Dates
---------- ---------- ---------- ---------- -------------------------------------
Jack       Shoes      Tie        Glass      01/01/2011, 02/01/2011, 03/03/2011
Peggy      Shoe       Skirt      NULL       02/02/2012, 02/12/2013
Devart
  • 119,203
  • 23
  • 166
  • 186