Consolidating Multiple Queries

Question

Hi,

Running SQL Server 2017. I have the following query:

SELECT
    T1.ColumnA,
    T2.ColumnB,
    T1.ColumnC
INTO
    #Temp1
FROM
    dbo.Test1 T1
JOIN
    dbo.Test2 T2 ON (T1.Test1ID = T2.Test2ID)
WHERE
    T1.Test1ID = X

Which gives me a temp table (#Temp1) with three columns, ColumnA, ColumnB and ColumnC and a single row. After creating the first temp table I do the following:

SELECT
    D1.Column1,
    D1.Column2
INTO
    #Temp2
FROM
    (
    SELECT
        'Column A' AS Column1,
        [ColumnA] AS Column2
    FROM
        #Temp1
    UNION
    SELECT
        'Column B' AS Column1,
        [ColumnB] AS Column2
    FROM
        #Temp1
    UNION
    SELECT
        'Column C' AS Column1,
        [ColumnC] AS Column2
    FROM
        #Temp1
    ) D1

Is there a way that I could accomplish the same objective using a single query, as well as do so without the use of temporary tables?

Any assistance is greatly appreciated!

Best Regards
B

Answer 1

I think that you can also consider UNPIVOT:

select Column1, Column2
from (
    select
        T1.ColumnA as [Column A],
        T2.ColumnB as [Column B],
        T1.ColumnC as [Column C]
    from  Test1 T1
    join Test2 T2 on T1.Test1ID = T2.Test2ID
    where T1.Test1ID = X
) t
unpivot
(
    Column2 for Column1 in ([Column A], [Column B], [Column C])
) u

Answer 2

Another way

SELECT
    CASE WHEN n.Number = 1 THEN 'Column A'
      WHEN n.Number = 2 THEN 'Column B'
      WHEN n.Number = 3 THEN 'Column C'
      END AS Column1,
    CASE WHEN n.Number = 1 THEN T1.ColumnA
      WHEN n.Number = 2 THEN T2.ColumnB
      WHEN n.Number = 3 THEN T1.ColumnC
      END AS Column2
FROM
    dbo.Test1 T1
JOIN
    dbo.Test2 T2 ON (T1.Test1ID = T2.Test2ID)
CROSS JOIN (SELECT 1 AS Number UNION ALL SELECT 2 UNION ALL SELECT 3) n
WHERE
    T1.Test1ID = X

Tom

Answer 3

Hi @VDT-7677 ,

Welcome to the Microsoft TSQL Q&A Forum!

You can use common table expression.

Please refer to:

 ;WITH cte  
 as(SELECT T1.ColumnA,T2.ColumnB,T1.ColumnC  
 FROM dbo.Test1 T1  
 JOIN dbo.Test2 T2 ON (T1.Test1ID = T2.Test2ID)  
 WHERE T1.Test1ID = X)  
 ,cte2  
 as( SELECT 'Column A' AS Column1,[ColumnA] AS Column2 FROM cte  
     UNION  
     SELECT 'Column B' AS Column1,[ColumnB] AS Column2 FROM cte  
     UNION  
     SELECT 'Column C' AS Column1,[ColumnC] AS Column2 FROM cte)  
  
 SELECT Column1,Column2 FROM cte2  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.