CODEWITHSUNDEEP
sql-servert-sql
Reto E.
Reto E.

Reputation: 119

select result to multiple columns

Unfortunatly I have to provide a query result in a 3 column format for a external application.

Example:

DECLARE @tmp TABLE ( id        INT
,                    somevalue VARCHAR(30) )

INSERT @tmp
VALUES ( 1, 'Text1' )
,      ( 2, 'Text2' )
,      ( 3, 'Text3' )
,      ( 4, 'Text4' )
,      ( 5, 'Text5' )
,      ( 6, 'Text6' )
,      ( 7, 'Text7' )
,      ( 8, 'Text8' )
,      (12, 'Text12')
,      (13, 'Text13')

SELECT *
FROM @tmp

But I need a resultset like this:

|ID1 |SOMEVALUE1|ID2 |SOMEVALUE2|ID3 |SOMEVALUE3|
|1   |Text1     |2   |Text2     |3   |Text3     |
|4   |Text4     |5   |Text5     |6   |Text6     |
|7   |Text7     |8   |Text8     |12  |Text12    |
|13  |Text13    |null|null      |null|null      |

What is the best way to format a result set from a flat table in that manner?

Upvotes: 0

Views: 1659

Answers (3)

Tyron78
Tyron78

Reputation: 4187

DECLARE @tmp TABLE ( id        INT
,                    somevalue VARCHAR(30) )

INSERT @tmp
VALUES ( 1, 'Text1' )
,      ( 2, 'Text2' )
,      ( 3, 'Text3' )
,      ( 4, 'Text4' )
,      ( 5, 'Text5' )
,      ( 6, 'Text6' )
,      ( 7, 'Text7' )
,      ( 8, 'Text8' )
,      ( 9, 'Text9' )

;WITH cte AS(
  SELECT *, ROW_NUMBER() OVER (ORDER BY id)%3 rn3
    FROM @tmp
),
cteR1 AS(
  SELECT c1.id, c1.somevalue, ROW_NUMBER() OVER (ORDER BY c1.id) AS rn
    FROM cte c1
    WHERE c1.rn3 = 1
),
cteR2 AS(
  SELECT c1.id, c1.somevalue, ROW_NUMBER() OVER (ORDER BY c1.id) AS rn
    FROM cte c1
    WHERE c1.rn3 = 2
),
cteR3 AS(
  SELECT c1.id, c1.somevalue, ROW_NUMBER() OVER (ORDER BY c1.id) AS rn
    FROM cte c1
    WHERE c1.rn3 = 0
)
SELECT R1.id, R1.somevalue, R2.id, R2.somevalue, R3.id, R3.somevalue
  FROM cteR1 R1
  JOIN cteR2 R2 ON R1.rn = R2.rn
  JOIN cteR3 R3 ON R1.rn = R3.rn

Upvotes: 0

BeanFrog
BeanFrog

Reputation: 2315

First set up a cte using your table, and a row_number column in case you have gaps in your id column. I've ordered by id, as I've assumed that's the ordering you want.

Then, join on that cte 3 times, one for each of the column groups you need. The first column will have row_numbers 1,4,7 etc - basically each multiple of 3, plus one. The other columns can be found because they are one and two rows after that.

with cte as (
    SELECT ROW_NUMBER() over (order by id) as rowno, *
    FROM @tmp
)
select a.id ID1, a.somevalue SOMEVALUE1, b.id ID2, b.somevalue SOMEVALUE2, c.id ID3, c.somevalue SOMEVALUE3
from cte a
left join cte b on b.rowno = a.rowno + 1
left join cte c on c.rowno = a.rowno + 2
where a.rowno % 3 = 1

If you don't have a perfect multiple of 3 in entries, you will just get nulls in the additional columns.

Upvotes: 3

MtwStark
MtwStark

Reputation: 4048

In this way you will spread values on 3 columns

WITH
r as (
    select *, ROW_NUMBER() over (order by id) n
    from tmp
)
SELECT T1.ID ID1, T1.SOMEVALUE SOMEVALUE1, T2.ID ID2, T2.SOMEVALUE SOMEVALUE2, T3.ID ID3, T3.SOMEVALUE SOMEVALUE3
FROM r T1
LEFT JOIN r t2 ON (T2.n = T1.n+1) 
LEFT JOIN r t3 ON (T3.n = t2.n+1)
WHERE (T1.n % 3) = 1 
ORDER BY T1.id

Upvotes: 3

Related Questions