sql server: join rows based on id

Question

I am trying to basically get all the tags for a specific merchant, and I am hoping to group the tags based on the merchant id (see below for what I'm getting with the current query and what I'm hoping to get). One merchant could have multiple of tags, but each row for Tag only has one merchant. Also merchants and tags are associated through a table merchanttags which just holds the id of the tag and the id of the merchant.

Query

select m.MerchantId, m.MerchantName, t.TagName
from Merchant m 
inner join MerchantTags mt on m.MerchantId=mt.MerchantId 
inner join tag t on mt.TagId=t.TagId 
where m.MerchantId=162

Result

MerchantId | MerchantName | TagName  
162        |   merchant   |  tag1
162        |   merchant   |  tag2

Desired Result

MerchantId | MerchantName | TagName | TagName  
162        |   merchant   |  tag1   | tag2

Or

MerchantId | MerchantName | TagName   
162        |   merchant   |  tag1, tag2

This might help you http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — tucaz, Jul 20 '15 at 21:03

score 0 · Answer 1 · answered Jul 20 '15 at 21:36

Test Data

DECLARE @Table TABLE (MerchantId INT, MerchantName VARCHAR(20), TagName VARCHAR(20))
INSERT INTO @Table VALUES   
(162 ,'merchant','tag1'),
(162 ,'merchant','tag2')

Query

SELECT t.MerchantId
      ,t.MerchantName
      ,STUFF((SELECT ',' + TagName
              FROM @Table 
              WHERE t.MerchantId   = MerchantId
                AND t.MerchantName = MerchantName
              FOR XML PATH(''),TYPE)
              .value('.','NVARCHAR(MAX)'),1,1,'') AS TagName
FROM @Table t 
GROUP BY t.MerchantId
      ,t.MerchantName

Result

╔════════════╦══════════════╦═══════════╗
║ MerchantId ║ MerchantName ║  TagName  ║
╠════════════╬══════════════╬═══════════╣
║    162     ║   merchant   ║ tag1,tag2 ║
╚════════════╩══════════════╩═══════════╝

Udaan · Answer 2 · 2015-07-21T02:33:19.577

Another Way First of all I think it'll be better if you put your Table Structure. Assume your tables are something like this

DECLARE @Merchants  TABLE (MerchantId INT, MerchantName VARCHAR(20))
INSERT INTO @Merchants  VALUES  
 (162 ,'merchant1')
,(163 ,'merchant2');

DECLARE @Tags TABLE (TagId INT, TagName VARCHAR(20))
INSERT INTO @Tags  VALUES   
 (1 ,'Tag01')
,(2 ,'Tag02')
,(3 ,'Tag03');

DECLARE @MerchantsTags TABLE (MerchantId INT, TagId INT)
INSERT INTO @MerchantsTags  VALUES   
 (162 ,1)
,(162 ,2)
,(163 ,1)
,(163 ,2)
,(163 ,3);

Then you can

SELECT MT.MerchantId,T.TagId,T.TagName
INTO #TMT
FROM @MerchantsTags MT
LEFT JOIN @Tags T ON MT.TagId = T.TagId


SELECT DISTINCT M.MerchantId,M.MerchantName,T1.TagName AS 'Tag1',T2.TagName AS 'Tag2',T3.TagName AS 'Tag3'
FROM @Merchants M
LEFT JOIN #TMT T1 ON M.MerchantId = T1.MerchantId AND T1.TagId = 1
LEFT JOIN #TMT T2 ON M.MerchantId = T2.MerchantId AND T2.TagId = 2
LEFT JOIN #TMT T3 ON M.MerchantId = T3.MerchantId AND T3.TagId = 3
--LEFT JOIN #TMT T4 ON M.MerchantId = T4.MerchantId AND T4.TagId = 4

DROP TABLE #TMT

If you want the Tags in a single column you can replace your SELECT with something like this

SELECT DISTINCT M.MerchantId,M.MerchantName,COALESCE(T1.TagName,'') +','+COALESCE(T2.TagName,'')+','+COALESCE(T3.TagName,'') AS 'Tag'

Here is SQLFiddle Version

sql server: join rows based on id

2 Answers2

Test Data

Query

Result