SQLTeam.com | Weblogs | Forums

How to change row to columns in SQL


#1

Hallo,

I am learning myself to program with SQL and I have a problem which I can't solve.
I have a few rows which I need to transfer to columns, see below.

The table looks like this
Order sub ID status
609011 01 01 R
609011 01 02 P
609011 01 03 R
There can be more ID's per Order en sub.

I like to choose the ID, which I will need in a column, like below.
In this example only ID 01 and 02.

Order sub ID Status ID Status
609011 01 01 R 02 P

I hope you understand what I mean, sorry if it is not all clear.
Kind regards,
Marco


#2

[CODE]
DECLARE @tbl table([Order] int, sub varchar(10), ID varchar(10), [status] char(1));
INSERT @tbl ([Order], sub, ID, status)
VALUES
(609011, '01', '01', 'R')
, (609011, '01', '02', 'P')
, (609011, '01', '03', 'R');

SELECT T1.[Order], T1.sub, t1.ID [01ID], t1.[status] [01status], T2.ID [02ID], T2.[status] [02status]
FROM @tbl t1
JOIN @tbl t2 ON
t2.[Order] = t1.[Order]
AND t2.sub = t1.sub
WHERE T1.ID = '01'
AND T2.ID = '02';
[/CODE]


#3

Thank you this solves the problem, I understand now.
I really appreciate this.