SQLTeam.com | Weblogs | Forums

6 rows 3 columns to 2 rows 8 columns

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

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

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')

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

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

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

2 Likes