Using coalesce in a single table

Question

Using coalesce in a single table

chozhanvijay 23273

Old Hand

Points: 365
More actions
August 24, 2016 at 10:52 am

#316660

hi,
I would like to get the below result from the table. Any help will be appreciated.
Table
1Brand1
2Brand1
3Brand2
3Brand3
3Brand4
3Brand5
Result
1Brand1
2Brand1
3Brand3,Brand4,Brand5
thanks
Vijay

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Why do you think that you need coalesce? What do you think coalesce does? If you're not sure, read the following link: https://msdn.microsoft.com/en-us/library/ms190349.aspx

To solve your problem, you might want to read the following article and come back for specific questions.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

chozhanvijay 23273 Old Hand Points: 365 More actions · Answer 2

hi Luis,

I think i should use the STUFF instead of the coalesce here. As coalesce is for returning the non null values. Can you please help me out with the script for the below.

Actually i have two tables

ItemTable

---------------------------

ItemIDBrandID

1 1

2 2

3 2

3 3

3 4

3 5

Brand Table

----------------

ID BrandName

1Brand1

2Brand2

3Brand3

4Brand4

5Brand5

6Brand6

Result

-------

ItemID BrandNames

1 Brand1

2 Brand1

3 Brand3,Brand4,Brand5,Brand6

Thanks in Advance

Vijay

chozhanvijay 23273 Old Hand Points: 365 More actions · Answer 3

hi,

To add on the above, I have grouped the above two input tables into a single table. So i just want to get the below result froma single table.

Table

1Brand1

2Brand1

3Brand2

3Brand3

3Brand4

3Brand5

Result

1Brand1

2Brand1

3Brand3,Brand4,Brand5

Thanks

Vijay

Sue_H SSC Guru Points: 90860 More actions · Answer 4

You keep changing the tables and don't provide any DDL which makes it difficult.

If you have one table with ID and Brand, one way to do a stuff for this would be:

SELECT DISTINCT ID,

Brand = STUFF(( SELECT ',' + Brand

FROM YourTable AS T2

WHERE T2.ID = T1.ID

ORDER BY Brand

FOR XMLPATH('')), 1, 1, '')

FROM YourTable AS T1;

Sue

chozhanvijay 23273 Old Hand Points: 365 More actions · Answer 5

hi,

Ya just got this solution few mins back. thanks for your help.

Regards

vijay

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

chozhanvijay 23273 (8/24/2016)
hi Luis,
I think i should use the STUFF instead of the coalesce here. As coalesce is for returning the non null values. Can you please help me out with the script for the below.

I saw that you got a solution. However, I'm still worried thinking that you might not understand how it works. Neither STUFF or COALESCE are used to concatenate values. Would you be able to explain how this works to someone else? It's all explained in the article, so be sure to read it carefully (is not long).

Also, using DISTINCT is expensive. In this case, you should try using GROUP BY.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Luis Cazares (8/24/2016)
Also, using DISTINCT is expensive. In this case, you should try using GROUP BY.

Which will behave exactly the same as the DISTINCT. Since he wants distinct values, DISTINCT is the correct thing to use.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

GilaMonster (8/24/2016)
Luis Cazares (8/24/2016)
Also, using DISTINCT is expensive. In this case, you should try using GROUP BY.
Which will behave exactly the same as the DISTINCT. Since he wants distinct values, DISTINCT is the correct thing to use.

Not really. The results will be the same, but the process won't.

Using DISTINCT, it will generate the concatenated values and then remove duplicates. Using GROUP BY, it will remove the duplicates and the concatenate the values. It can be a big difference in performance, depending on the rows.

On most cases, I'd agree with the premise of using DISTINCT to get unique rows, but not for this.

EDIT: An alternative, is to use the method shown on the article to get unique key values and then concatenate.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

So put the GROUP BY inside the subquery, not replacing the DISTINCT in the outer query? It's not clear from the post.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183698 More actions · Answer 10

I mean that instead of the proposed solution:

SELECT DISTINCT

ID,

Brand = STUFF(( SELECT ',' + Brand

FROM YourTable AS T2

WHERE T2.ID = T1.ID

ORDER BY Brand

FOR XML PATH('')), 1, 1, '')

FROM YourTable AS T1;

Use one of the following options:

--Option 1

SELECT

ID,

Brand = STUFF(( SELECT ',' + Brand

FROM YourTable AS T2

WHERE T2.ID = T1.ID

ORDER BY Brand

FOR XML PATH('')), 1, 1, '')

FROM YourTable AS T1

GROUP BY ID;

--Option 2

WITH CTE AS(

SELECT DISTINCT ID

FROM YourTable

)

SELECT

ID,

Brand = STUFF(( SELECT ',' + Brand

FROM YourTable AS T2

WHERE T2.ID = CTE.ID

ORDER BY Brand

FOR XML PATH('')), 1, 1, '')

FROM CTE;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sue_H SSC Guru Points: 90860 More actions · Answer 11

SELECT ID ,

Brand = STUFF(( SELECT',' + Brand

FROM YourTable AS P2

WHERE P2.ID = P1.ID

ORDER BY Brand

FOR XMLPATH('')

), 1, 1, '')

FROM YourTable AS P1

Group by ID;