-4

Please refer to the image below:

MSSQL server 2008 QUERY MULTIPLE VALUES IN COMMA SEPARATED VALUES BY ONLY ONE SELECTED NAME

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
gunter
  • 3
  • 4
  • 3
    Kindly edit your post and clarify your question. Posting an image is not enough, – Felix Pamittan Mar 11 '15 at 01:28
  • possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) – jpw Mar 11 '15 at 01:38

2 Answers2

1

Create your table taro:

SELECT * INTO taro
FROM 
(
    SELECT 1111 AS [C no.], 'ken' AS [Name], 'shiro' AS Item, '3/3/2000 12:22' AS [Date], '$25' AS Amount
    UNION ALL
    SELECT 1111 AS [C no.], 'ken' AS Name, 'aeshte' AS Item, '3/3/2000 12:22' AS [Date], '$25' AS Amount
    UNION ALL 
    SELECT 1111 AS [C no.], 'ken' AS Name, 'taro' AS Item, '3/3/2000 12:22' AS [Date], '$25' AS Amount
    UNION ALL
    SELECT 2222,'yakumo','tesen','3/4/2000 12:22','$22'
    UNION ALL
    SELECT 2222,'yakumo','aoie','3/4/2000 12:22','$22'
    UNION ALL
    SELECT 3333,'kage','manase','3/5/2000 12:22','$21'
) A

Group it by your columns and put items together separated by commas

SELECT
    [C No.],
    Name,
    [Date],
    Amount,
    STUFF((
    SELECT ',' + A.Item
    FROM taro A
    WHERE   A.[C no.] = B.[C no.]
    AND     A.Name = B.Name
    AND     A.[Date] = B.[Date]
    AND     A.[Amount] = B.[Amount]
    ORDER BY item
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS ItemValue
FROM taro B
GROUP BY [C No.],Name,[Date],Amount

Results:

C No.       Name   Date           Amount ItemValue
----------- ------ -------------- ------ --------------------
1111        ken    3/3/2000 12:22 $25    aeshte,shiro,taro
2222        yakumo 3/4/2000 12:22 $22    aoie,tesen
3333        kage   3/5/2000 12:22 $21    manase
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • what is the difference between ,1,1 and ,1,2? – ajbee Mar 11 '15 at 02:23
  • The 1,2 adds a space in front of each item including the first one which i didn't want so I use 1,1 – Stephan Mar 11 '15 at 03:37
  • 1
    Hi sir its been a year, sir we are a beginner that time and my boss want to fix that particular tables, your code is perfect and useful, but I cannot vote up because my reputation is just 1 – gunter Jul 11 '16 at 10:00
  • Don't worry about it. It's just great to hear that you were able to use it and it worked for you. – Stephan Jul 12 '16 at 14:02
0
DECLARE @Table1 TABLE(CNo INT, name nvarchar(20), item nvarchar(20),Date datetime, Value INT)
INSERT INTO @Table1 VALUES  (1111,'ken','shiro', GETDATE(), 25),
                        (1111,'ken', 'aeshte', GETDATE(), 25),
                        (1111,'ken', 'taro', GETDATE(), 25),
                        (2222,'yakumo', 'tesen', GETDATE(), 25),
                        (2222,'yakumo', 'aole', GETDATE(), 25),
                        (3333,'kage', 'manase', GETDATE(), 25)


SELECT  CNo 
   ,STUFF((SELECT ', ' + CAST((item) AS VARCHAR(10)) [text()]
     FROM @Table1 
     WHERE CNo = t.CNo  and date = t.Date and Value = t.Value and name = t.name
     FOR XML PATH(''), TYPE)
    .value('.','NVARCHAR(MAX)'),1,2,' ') Item, 
    date, value, name
FROM @Table1 t
GROUP BY CNo,  Date, value, name
niketshah90
  • 199
  • 1
  • 10