Merge more than two rows into one row and place right data into right column

Question

First of I'm sorry if the question heading seems confusing to you guys but let me show you what I want.

Let's say I have a table called topic.

Topic_ID | Parent_Topic_ID | Topic_Text           | lang_culture
---------|-----------------|----------------------|--------------
    1    |       0         | Newton's Law         |   en-US
    2    |       1         | First Law            |   en-US

Now I have another table named translated_topic where the same topic is stored in different languages

Topic_ID | text                | lang_culture
---------|-------------------- |--------------
   1     | न्यूटन का नियम        |   hi-IN
   1     | loi de newton       |   fr-FR

   2     | पहला कानून           |  hi-IN
   2     | Première loi        |  fr-FR

Now, I want the show output like this...

Topic_ID | Topic_Text         | lang_culture | hi-In            | fr-FR     
---------|--------------------|--------------|------------------ |-------------
    1    |    Newton's Law    |en-US         | न्यूटन का नियम      | loi de newton
    2    |    First Law       | en-US        | पहला कानून         |Première loi

How can I achieve this output in SQL-server?

What I am getting now,

 SELECT td.topic_id,
           td.Topic_Text AS MainText,
           td.lang_culture,
           tt.text,
           tt.lang_culture
    FROM dbo.topic td
        LEFT JOIN dbo.translated_topic tt
            ON td.topic_id = tt.topic_id;

But after that don't know how to get my desired result?

Sir, I followed this https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server but could not able to figure out how to write it properly. If someone could help me then it will be much nice — Tay, Jul 18 '19 at 14:02

score 1 · Accepted Answer · answered Jul 18 '19 at 14:06

You can you PIVOT as below-

SELECT *
FROM
(
    SELECT A.Topic_ID,A.Parent_Topic_ID,A.Topic_Text,A.lang_culture lang_culture,B.lang_culture B,B.text
    FROM Table1 A
    INNER JOIN Table2 B ON A.Topic_ID = B.Topic_ID
)P 
PIVOT
(
    MAX(text)
    FOR B IN ([hi-IN],[fr-FR])
) PVT

Merge more than two rows into one row and place right data into right column

1 Answers1