Concatenate multiple rows to one big row-CodePudding

I have two tables in my database, with this sample data:

table 1: main

m_id  eID  sDate       eDate
1     75   2022-12-01  NULL

table 2: details

m_id  cc_id  cu_id  perc
1     1      1      40
1     1      2      40
1     1      3      20

Here's what I would like to achieve in SQL Server:

m_id  eID  sDate       eDate  cc_id^1  cu_id^1  perc^1  cc_id^2  cu_id^2  perc^2  cc_id^3  cu_id^3  perc^3
1     75   2022-12-01  NULL   1        1        40      1        2        40      1        3        20

So, the three rows in the 'details' table should be concatenated to the single row in the 'main' table.

I read about and tried the PIVOT Function, but I think it's not exactly what I'm looking for. To me, it seems PIVOT is using each unique value in the 'details' table as column header and then counts the number of instances of it. For example like this:

m_id  eID  sDate       eDate  40
1     75   2022-12-01  NULL   2

So, basically using 40 as a column header and then fill its value with 2, as there are two instances of 40 in the perc column in the 'details' table.

I spent an entire day searching via Google and trying the PIVOT function without luck.

CodePudding user response：

The only way I can think of it is this, (I would imagine that someone else will have a better solution!), BUT you NEED to know the values of [cu_id] ahead of time, and without knowing what the key is on Table 2, this is the best that I can do:

SELECT  
    Main.m_id,  
    Main.eID,  
    Main.sDate, 
    Main.eDate, 
    [Details1].[cc_id] AS [cc_id^1], 
    [Details1].[cu_id] AS [cu_id^1],
    [Details1].[perc] AS [perc^1] ,
    [Details2].[cc_id] AS [cc_id^2], 
    [Details2].[cu_id] AS [cu_id^2],
    [Details2].[perc] AS [perc^2], 
    [Details3].[cc_id] AS [cc_id^3], 
    [Details3].[cu_id] AS [cu_id^3],
    [Details3].[perc] AS [perc^3]  
FROM [main] AS Main 
    INNER JOIN [Details] AS [Details1] ON Main.[m_id] = [Details1].[m_id]
    INNER JOIN [Details] AS [Details2] ON Main.[m_id] = [Details1].[m_id] 
    INNER JOIN [Details] AS [Details3] ON Main.[m_id] = [Details3].[m_id]
WHERE
    [Details1].[cu_id] = 1
    AND
    [Details2].[cu_id] = 2 
    AND
    [Details3].[cu_id] = 3

I get the correct results, IF I know ahead of time what the values of cu_id are.

CodePudding user response：

Many thanks for your help, Larnu and SWR!

@SWR unfortunately, the values for cu_id are not known ahead of time. They basically could be any integer. But thanks for your help though!

@Larnu, Thanks to you as well! I didn't post any of my pivot queries, as they got me nowhere near to the expected result. With the link to your other post, I was able to build this query.

WITH RNs AS(
    SELECT h.clv_id, h.bed_id, h.wns_id, h.dvb_id, h.con_id, h.DateFrom, h.DateTo, d.kpl_id, d.kdr_id, d.dimension3, d.dimension4, d.dimension5, d.percentage       
      ,ROW_NUMBER() OVER (PARTITION BY d.clv_id ORDER BY (SELECT NULL)) AS RN
    FROM clv_hoofd h INNER JOIN clv_details d ON h.clv_id = d.clv_id
    WHERE h.clv_id = 1094)
SELECT clv_id, bed_id, wns_id, dvb_id, con_id, DateFrom, DateTo, 
       CASE RN WHEN 1 THEN kpl_id END AS 'kpl^1',
       CASE RN WHEN 1 THEN kdr_id END AS 'kdr^1',
       CASE RN WHEN 1 THEN dimension3 END AS 'dim3^1',
       CASE RN WHEN 1 THEN dimension4 END AS 'dim4^1',
       CASE RN WHEN 1 THEN dimension5 END AS 'dim5^1',
       CASE RN WHEN 1 THEN percentage END AS 'perc^1',

       CASE RN WHEN 2 THEN kpl_id END AS 'kpl^2',
       CASE RN WHEN 2 THEN kdr_id END AS 'kdr^2',
       CASE RN WHEN 2 THEN dimension3 END AS 'dim3^2',
       CASE RN WHEN 2 THEN dimension4 END AS 'dim4^2',
       CASE RN WHEN 2 THEN dimension5 END AS 'dim5^2',
       CASE RN WHEN 2 THEN percentage END AS 'perc^2',

       CASE RN WHEN 3 THEN kpl_id END AS 'kpl^3',
       CASE RN WHEN 3 THEN kdr_id END AS 'kdr^3',
       CASE RN WHEN 3 THEN dimension3 END AS 'dim3^3',
       CASE RN WHEN 3 THEN dimension4 END AS 'dim4^3',
       CASE RN WHEN 3 THEN dimension5 END AS 'dim5^3',
       CASE RN WHEN 3 THEN percentage END AS 'perc^3',

       CASE RN WHEN 4 THEN kpl_id END AS 'kpl^4',
       CASE RN WHEN 4 THEN kdr_id END AS 'kdr^4',
       CASE RN WHEN 4 THEN dimension3 END AS 'dim3^4',
       CASE RN WHEN 4 THEN dimension4 END AS 'dim4^4',
       CASE RN WHEN 4 THEN dimension5 END AS 'dim5^4',
       CASE RN WHEN 4 THEN percentage END AS 'perc^4',

       CASE RN WHEN 5 THEN kpl_id END AS 'kpl^5',
       CASE RN WHEN 5 THEN kdr_id END AS 'kdr^5',
       CASE RN WHEN 5 THEN dimension3 END AS 'dim3^5',
       CASE RN WHEN 5 THEN dimension4 END AS 'dim4^5',
       CASE RN WHEN 5 THEN dimension5 END AS 'dim5^5',
       CASE RN WHEN 5 THEN percentage END AS 'perc^5',

       CASE RN WHEN 6 THEN kpl_id END AS 'kpl^6',
       CASE RN WHEN 6 THEN kdr_id END AS 'kdr^6',
       CASE RN WHEN 6 THEN dimension3 END AS 'dim3^6',
       CASE RN WHEN 6 THEN dimension4 END AS 'dim4^6',
       CASE RN WHEN 6 THEN dimension5 END AS 'dim5^6',
       CASE RN WHEN 6 THEN percentage END AS 'perc^6'
FROM RNs R

This returns six rows, doing it like this:

kpl_id^1 kdr_id^1 dimension3^1 dimension4^1 dimension5^1 percentage^1 kpl_id^2 kdr_id^2 dimension3^2 dimension4^2 dimension5^2 percentage^2
2        1        00000        01           NVT          50.00        NULL     NULL     NULL         NULL         NULL         NULL
NULL     NULL     NULL         NULL         NULL         NULL         1        1        00000        02           NVT          10.00
...

This query comes from my real world database, hence the different names for tables and columns.

Thanks in advance!