How to display the multiple values in a single column with line by line in SQL SERVER 2008 R2?

Question

I'm using SQL SERVER 2008 R2. I want to get the multiple values and show it in the single column with line by line at the same time, if the values present otherwise it shows some values and might be the possibility of '12 values' on a single column.

Sample screenshot 2

For Example, The screen Shot below, it shows records for the same data except the comments. I want to display as the single record, but the comments should show the both values that is ASN Timeliness, ASN Accuracy, problem reports. The three values are displayed line by line using the same shipsite "0096a".

Sample screenshot 1

My sample query,

SELECT 
    D30.SPGD30_SHIP_SITE_C AS SHIPSITE,  
    D30.SPGD30_RATING_MONTH_Y AS RATINGMONTH,  
    D30.SPGD30_PRIOR_SCORE_R AS PRIOR, 
    D30.SPGD30_REVISED_SCORE_R AS REVISED,  
    CASE WHEN (CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) > 0 ) THEN CONVERT( VARCHAR(8), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X AS DATETIME) , 1) ELSE D30.SPGD30_TRACKED_ADJUSTMENT_X END ADJUSTMENTS,  
    J02.SPGJ02_MSG_CODE_X AS COMMENTS,  
    D30.SPGD30_LAST_TOUCH_Y AS LASTUPDATED,  
    D30.SPGD30_LAST_TOUCH_C AS LASTUPDATEDCDSID   
FROM  
    CSPGD30_TRACKING D30, 
    CSPGD31_TRACKING_RATING_ELEMNT D31,  
    CSPGA04_RATING_ELEMENT_MSTR A04 , 
    CSPGJ02_MSG_OBJ J02  
WHERE  
    D30.SPGA02_BUSINESS_TYPE_C = D31.SPGA02_BUSINESS_TYPE_C  
AND 
    D30.SPGA03_REGION_C = D31.SPGA03_REGION_C  
AND 
    D30.SPGD30_SHIP_SITE_C = D31.SPGD30_SHIP_SITE_C  
AND 
    D30.SPGD30_RATING_MONTH_Y = D31.SPGD30_RATING_MONTH_Y  
AND 
    D30.SPGD30_TRACKED_ADJUSTMENT_X = D31.SPGD30_TRACKED_ADJUSTMENT_X  
AND 
    D30.SPGD30_LAST_TOUCH_Y = D31.SPGD30_LAST_TOUCH_Y  
AND 
    D31.SPGA04_RATING_ELEMENT_D = A04.SPGA04_RATING_ELEMENT_D  
AND 
    A04.SPGJ02_MSG_K = J02.SPGJ02_MSG_K  
AND 
    D30.SPGA02_BUSINESS_TYPE_C = 'serv'  
AND 
    D30.SPGA03_REGION_C = 'ap'  
AND 
    D30.SPGD30_SHIP_SITE_C = '0134a'  
ORDER BY   
    D30.SPGD30_SHIP_SITE_C ASC  ,
    D30.SPGD30_RATING_MONTH_Y DESC  ,
    D30.SPGD30_LAST_TOUCH_Y DESC

what is your desired result? there are many columns, what columns do you want to show? — John Woo, Feb 18 '13 at 09:39
I want to show a multiple values (12-rating elements) in a single column. For example if 8 rating elements present we show only 8 rating elements in a single column. — Adalarasan_Serangulam, Feb 18 '13 at 09:42
you should look at this: [http://stackoverflow.com/questions/1574407/how-to-concatenate-n-columns-into-one](http://stackoverflow.com/questions/1574407/how-to-concatenate-n-columns-into-one) — Dalex, Feb 18 '13 at 15:45
i am not sure what is your desired result. If you want to show multiple row data in a single column try using "distinct". if not then add some more info to the question. — Ankit Suhail, Feb 20 '13 at 09:06
@Adalarasan_Serangulam are you able to use Scalar-valued Functions? Does the solution have to be a self-contained query? — Jesse, Mar 25 '13 at 17:22
@Adalarasan_Serangulam Can you post some sample data and the desired result not as an image? Even better would be to create [SQL Fiddle](http://sqlfiddle.com/) with your table structures, sample data. — Taryn, Mar 25 '13 at 17:41
@bluefeet, do you have any idea aabout that.. i need your help at this point of time. — Adalarasan_Serangulam, Mar 26 '13 at 13:15
do you really need this as a SQL query or is this for display purposes on an SSRS report? — Moho, Mar 27 '13 at 19:20

Taryn · Answer 1 · 2013-03-31T00:39:00.833

I see two ways that you can do this.

First you can use FOR XML PATH and STUFF in a correlated subquery. This will concatenate the values from the CSPGJ02_MSG_OBJ into a single string:

SELECT D30.SPGD30_SHIP_SITE_C AS SHIPSITE,  
    D30.SPGD30_RATING_MONTH_Y AS RATINGMONTH,  
    D30.SPGD30_PRIOR_SCORE_R AS PRIOR, 
    D30.SPGD30_REVISED_SCORE_R AS REVISED,  
    CASE WHEN (CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) > 0 ) THEN CONVERT( VARCHAR(8), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X AS DATETIME) , 1) ELSE D30.SPGD30_TRACKED_ADJUSTMENT_X END ADJUSTMENTS,  
     STUFF((SELECT distinct '+ ' + J02.SPGJ02_MSG_CODE_X
            from CSPGJ02_MSG_OBJ J02
            where A04.SPGJ02_MSG_K = J02.SPGJ02_MSG_K 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') COMMENTS,
    D30.SPGD30_LAST_TOUCH_Y AS LASTUPDATED,  
    D30.SPGD30_LAST_TOUCH_C AS LASTUPDATEDCDSID   
FROM CSPGD30_TRACKING D30
INNER JOIN CSPGD31_TRACKING_RATING_ELEMNT D31
    ON D30.SPGA02_BUSINESS_TYPE_C = D31.SPGA02_BUSINESS_TYPE_C  
    AND D30.SPGA03_REGION_C = D31.SPGA03_REGION_C  
    AND D30.SPGD30_SHIP_SITE_C = D31.SPGD30_SHIP_SITE_C  
    AND D30.SPGD30_RATING_MONTH_Y = D31.SPGD30_RATING_MONTH_Y  
    AND D30.SPGD30_TRACKED_ADJUSTMENT_X = D31.SPGD30_TRACKED_ADJUSTMENT_X  
    AND D30.SPGD30_LAST_TOUCH_Y = D31.SPGD30_LAST_TOUCH_Y  
INNER JOIN CSPGA04_RATING_ELEMENT_MSTR A04
    ON D31.SPGA04_RATING_ELEMENT_D = A04.SPGA04_RATING_ELEMENT_D  
WHERE D30.SPGA02_BUSINESS_TYPE_C = 'serv'  
    AND D30.SPGA03_REGION_C = 'ap'  
    AND D30.SPGD30_SHIP_SITE_C = '0134a'  
ORDER BY D30.SPGD30_SHIP_SITE_C ASC, D30.SPGD30_RATING_MONTH_Y DESC, D30.SPGD30_LAST_TOUCH_Y DESC;

The second method is to use a CROSS APPLY with FOR XML PATH:

SELECT D30.SPGD30_SHIP_SITE_C AS SHIPSITE,  
    D30.SPGD30_RATING_MONTH_Y AS RATINGMONTH,  
    D30.SPGD30_PRIOR_SCORE_R AS PRIOR, 
    D30.SPGD30_REVISED_SCORE_R AS REVISED,  
    CASE WHEN (CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) > 0 ) THEN CONVERT( VARCHAR(8), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X AS DATETIME) , 1) ELSE D30.SPGD30_TRACKED_ADJUSTMENT_X END ADJUSTMENTS,  
    left(J02.comments, LEN(J02.comments)-1) AS COMMENTS,
    D30.SPGD30_LAST_TOUCH_Y AS LASTUPDATED,  
    D30.SPGD30_LAST_TOUCH_C AS LASTUPDATEDCDSID   
FROM CSPGD30_TRACKING D30
INNER JOIN CSPGD31_TRACKING_RATING_ELEMNT D31
    ON D30.SPGA02_BUSINESS_TYPE_C = D31.SPGA02_BUSINESS_TYPE_C  
    AND D30.SPGA03_REGION_C = D31.SPGA03_REGION_C  
    AND D30.SPGD30_SHIP_SITE_C = D31.SPGD30_SHIP_SITE_C  
    AND D30.SPGD30_RATING_MONTH_Y = D31.SPGD30_RATING_MONTH_Y  
    AND D30.SPGD30_TRACKED_ADJUSTMENT_X = D31.SPGD30_TRACKED_ADJUSTMENT_X  
    AND D30.SPGD30_LAST_TOUCH_Y = D31.SPGD30_LAST_TOUCH_Y  
INNER JOIN CSPGA04_RATING_ELEMENT_MSTR A04
    ON D31.SPGA04_RATING_ELEMENT_D = A04.SPGA04_RATING_ELEMENT_D  
CROSS APPLY
(
    select J02.SPGJ02_MSG_CODE_X  + ', '
    from CSPGJ02_MSG_OBJ J02
    where A04.SPGJ02_MSG_K = J02.SPGJ02_MSG_K  
    FOR XML PATH('')
) J02 (comments)
WHERE D30.SPGA02_BUSINESS_TYPE_C = 'serv'  
    AND D30.SPGA03_REGION_C = 'ap'  
    AND D30.SPGD30_SHIP_SITE_C = '0134a'  
ORDER BY D30.SPGD30_SHIP_SITE_C ASC, D30.SPGD30_RATING_MONTH_Y DESC, D30.SPGD30_LAST_TOUCH_Y DESC;

Note: You will notice that I changed your query to use JOIN syntax instead of the comma separated tables with the joins in the WHERE clause. This is standard ANSI syntax.

it is not working for me. it doesn't show line by line. so what can i do. it shows two records, (using SQL Server 2008 r2) — Adalarasan_Serangulam, Mar 27 '13 at 13:27
@Adalarasan_Serangulam My suggestion is to create a SQL Fiddle with your tables and some sample data or edit your post with the full table structure and sample data for each table. — Taryn, Mar 27 '13 at 13:49
@Adalarasan_Serangulam the SQL Fiddle will be best because then we will have a working data model. — Taryn, Mar 27 '13 at 14:47

Serge · Answer 2 · 2013-03-26T15:13:02.440

0

Using XML PATH('')

SELECT 
    D30.SPGD30_SHIP_SITE_C AS SHIPSITE,  
    D30.SPGD30_RATING_MONTH_Y AS RATINGMONTH,  
    D30.SPGD30_PRIOR_SCORE_R AS PRIOR, 
    D30.SPGD30_REVISED_SCORE_R AS REVISED,  
    CASE WHEN (CHARINDEX('-',D30.SPGD30_TRACKED_ADJUSTMENT_X) > 0 ) THEN CONVERT( VARCHAR(8), CAST(D30.SPGD30_TRACKED_ADJUSTMENT_X AS DATETIME) , 1) ELSE D30.SPGD30_TRACKED_ADJUSTMENT_X END ADJUSTMENTS,  
    STUFF(JO2.COMMENTS, 1, 1, '') AS COMMENTS,  
    D30.SPGD30_LAST_TOUCH_Y AS LASTUPDATED,  
    D30.SPGD30_LAST_TOUCH_C AS LASTUPDATEDCDSID   
FROM  
    CSPGD30_TRACKING D30 
CROSS JOIN CSPGD31_TRACKING_RATING_ELEMNT D31
CROSS JOIN CSPGA04_RATING_ELEMENT_MSTR A04
CROSS APPLY (
    SELECT
        ',' + ISNULL(JO2.SPGJ02_MSG_CODE_X, '')
    FROM CSPGJ02_MSG_OBJ JO2  
    WHERE A04.SPGJ02_MSG_K = JO2.SPGJ02_MSG_K  
    FOR XML PATH('')
) AS JO2 (COMMENTS)
WHERE  
    D30.SPGA02_BUSINESS_TYPE_C = D31.SPGA02_BUSINESS_TYPE_C  
AND 
    D30.SPGA03_REGION_C = D31.SPGA03_REGION_C  
AND 
    D30.SPGD30_SHIP_SITE_C = D31.SPGD30_SHIP_SITE_C  
AND 
    D30.SPGD30_RATING_MONTH_Y = D31.SPGD30_RATING_MONTH_Y  
AND 
    D30.SPGD30_TRACKED_ADJUSTMENT_X = D31.SPGD30_TRACKED_ADJUSTMENT_X  
AND 
    D30.SPGD30_LAST_TOUCH_Y = D31.SPGD30_LAST_TOUCH_Y  
AND 
    D31.SPGA04_RATING_ELEMENT_D = A04.SPGA04_RATING_ELEMENT_D  
AND 
    D30.SPGA02_BUSINESS_TYPE_C = 'serv'  
AND 
    D30.SPGA03_REGION_C = 'ap'  
AND 
    D30.SPGD30_SHIP_SITE_C = '0134a'  
ORDER BY   
    D30.SPGD30_SHIP_SITE_C ASC  ,
    D30.SPGD30_RATING_MONTH_Y DESC  ,
    D30.SPGD30_LAST_TOUCH_Y DESC

This one will make a comma separated list of comments.

edited Mar 26 '13 at 15:13

answered Mar 25 '13 at 15:33

Serge

6,554
5
30
56

I had written a 0 instead of an O. Nice to someone gave me a down vote for that... (Yeah, I guess the interesting part isn't the CROSS APPLY or the XML PATH but the Oes and the Zeroes...) – Serge Mar 26 '13 at 15:16
4

I did not down-vote, but can you explain why you're using CROSS JOIN and join conditions in the WHERE clause instead of proper, modern, and more intuitive INNER JOIN syntax? – Aaron Bertrand Mar 26 '13 at 15:38
In order to the query resembles the original as much as possible (since that might be the way the author like to read queries). I personally prefer inner joins. – Serge Mar 26 '13 at 15:42
2

@Serge if the OP is a beginner it might be best to steer them AWAY from that type of query. The original form in the question is even worse. – Aaron Bertrand Mar 26 '13 at 15:48
I don't know if the OP is a noob and I'm experienced enough to know standards/guidelines aren't always the best path to follow. – Serge Mar 26 '13 at 16:19
Could you tell me when would that be? – swasheck Mar 26 '13 at 16:27
Do the words "Exception to the rule", "Tricks" or "Constraints" ring a bell? – Serge Mar 26 '13 at 16:30
2

@Serge they do, I'm just wondering if you'd be so kind as to unveil when these things are best. – swasheck Mar 26 '13 at 16:36
7

@Serge `I'm experienced enough to know standards/guidelines aren't always the best path to follow.` Oh please enlighten us as to why a proper `INNER JOIN` is not the best path to follow in this case? What advantage does your `CROSS JOIN` offer, other than continue to encourage archaic coding practices that should most certainly be avoided? – Aaron Bertrand Mar 26 '13 at 16:40
> I personally prefer inner joins > aren't **always** the best path to follow Thanks for all the rep down btw, it'll teach keeping an open mind means trouble. – Serge Mar 26 '13 at 17:06
@Serge not seeing any rep down looks like a 0 to me. Definable didn't throw any on there. – Zane Mar 26 '13 at 17:27

Igor Lozovsky · Answer 3 · 2013-04-01T13:02:00.900

0

If you want to display multiple columns in one, you can write like this:

SELECT CAST([MyIntegerId] AS varchar(10)) + ' - ' + [Column1]+ ' - ' +CAST([MyDateTimeColumn] AS varchar(10)) + ' - ' + [Column2] AS 'My Merged Column'
FROM Mytable

If you want to group by multiple columns to single column as a string - this article should help you:

edited Apr 01 '13 at 13:02

answered Apr 01 '13 at 12:55

Igor Lozovsky

2,275
2
15
14

How to display the multiple values in a single column with line by line in SQL SERVER 2008 R2?

3 Answers3