6 rows 3 columns to 2 rows 8 columns

Transact-SQL
1

I currently have a simple select query
Select IDColumn,Column1,column2
from Table
Output :

IDColumn Column1 Column2

OM One Monday
TT Two Tuesday
TW Three Wednesday
FT Four Thursday
FF Five Friday
SS Six Saturday

I would like the desired ouput to be that each row takes 3 unique ID columns before creating a second row
Desired output
IDColumn Column1 Column2 Column3 Column 4 Column 5 Column 6 Column 7 Column 8
OM One Monday TT Two Tuesday TT Three Wednesday
FT Four Thursday FF Five Friday SS Six Saturday

A note: the data returned does not have to be in any particular order.So if Wednesday is first that is fine

2

If you'll post directly useable data (CREATE TABLE and INSERT statements), I'll post the code to do that.

3

These are the create and insert statements:

Create Table Weekdaystst(
ID nvarchar(3) Unique,
Column1 varchar(50),
Column2 varchar(50)
)

Insert INTO Weekdaystst Values('OM', 'One,','Monday'),
('TT' ,'Two','Tuesday'),
('TW', 'Three','Wednesday'),
('FT' ,'Four','Thursday'),
('FF', 'Five','Friday'),
('SS', 'Six','Saturday')

4

Insert INTO Weekdaystst Values('XX', 'Seven','Sunday')

SELECT 
    MAX(CASE WHEN row_num % 3 = 1 THEN ID END) AS ID1,
    MAX(CASE WHEN row_num % 3 = 1 THEN Column1 END) AS Column1,
    MAX(CASE WHEN row_num % 3 = 1 THEN Column2 END) AS Column2,
    MAX(CASE WHEN row_num % 3 = 2 THEN ID END) AS ID2,
    MAX(CASE WHEN row_num % 3 = 2 THEN Column1 END) AS Column3,
    MAX(CASE WHEN row_num % 3 = 2 THEN Column2 END) AS Column4,
    MAX(CASE WHEN row_num % 3 = 0 THEN ID END) AS ID3,
    MAX(CASE WHEN row_num % 3 = 0 THEN Column1 END) AS Column5,
    MAX(CASE WHEN row_num % 3 = 0 THEN Column2 END) AS Column6
FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY ID) AS row_num
    FROM dbo.Weekdaystst
) AS row_nums
GROUP BY (row_num - 1) / 3
5

Thank you so much.Scott. This has helped me sort out a headache and a half

6

You're welcome. Was this as good as the answer you got on Experts-Exchange for your q? :slight_smile:

2 Likes