CODEWITHSUNDEEP
sqlsql-server
user1828605
user1828605

Reputation: 1735

How to combine multiple rows with JSON PATH in SQL Server?

I have this dataset with 4 tables. I am trying to write the SQL query as following:

WITH test AS
(
    SELECT 
        (f.name), f.id, f.domain, s.link,
        (SELECT
             name,
             CASE
                WHEN name IN (1, 3, 8) THEN 1
                WHEN name IN (2, 6, 7) THEN 2
             END AS [group] 
         FROM tags 
         WHERE corporate_statement_link_id = s.id 
         FOR JSON PATH) AS tags
    FROM 
        fortune1000_companies f 
    LEFT JOIN 
        search_results s ON f.id = s.company_id
    LEFT JOIN 
        corporate_statements c ON s.id = c.corporate_statement_link_id 
    WHERE 
        c.corporate_statement = 1 
        AND s.domain LIKE CONCAT('%', f.domain, '%')
)
SELECT name, link, tags 
FROM test

but this produces the result where company names are duplicated because of the differences in link. For e.g., UnitedHeath Group (rows 4 & 5) is in two rows because the link is different. I want the result in such a way that the company name is shown just once, and tags are in the same group together. I don't need link to be shown; only included for this SO.

enter image description here

Upvotes: 0

Views: 2210

Answers (1)

user1828605
user1828605

Reputation: 1735

I think I figured it out.

This is what I did, and it gave me the answer I was looking for.

select name
    , (STUFF((SELECT t.[name] from tags t 
        inner join search_results s 
        on s.id = t.corporate_statement_link_id
        where f.id = s.company_id
        FOR JSON PATH),1,2,'[{')) as ts
from [fortune1000_companies] f
where f.id between 1 and 101

I got the help from here

Upvotes: 1

Related Questions