Rows as Columns

[Guinness Mann]

I know this is a self join, but I can't remember exactly how it goes.
Could someone help me out?

create table A
{
int idA,
varchar(30) dataA
}

create table B
{
int idB,
varchar(30) dataB
}

create table A_B
{
int idA references A(idA),
int idB references B(idB)
}

insert into A values(1, "foobar")
insert into A values(2, "barfoo")

insert into B values(1, "a")
insert into B values(2, "b")
insert into B values(3, "c")
insert into B values(4, "d")
insert into B values(5, "e")
insert into B values(6, "f")
insert into B values(7, "g")
insert into B values(8, "h")

insert into A_B values (1, 1)
insert into A_B values (1, 2)
insert into A_B values (1, 3)
insert into A_B values (1, 4)

desired results

foobar a b c d

Thanks,

-- Rick

[Guinness Mann]

Geez, have I been writing too much C code, or what. Here's the T-SQL
statements that actually work. :-)

create table #A
(
idA int,
dataA varchar(30)
)

create table #B
(
idB int,
dataB varchar(30)
)

create table ##A_B
(
idA int references A(idA),
idB int references B(idB)
)

insert into #A values(1, 'foobar')
insert into #A values(2, 'barfoo')

insert into #B values(1, 'a')
insert into #B values(2, 'b')
insert into #B values(3, 'c')
insert into #B values(4, 'd')
insert into #B values(5, 'e')
insert into #B values(6, 'f')
insert into #B values(7, 'g')
insert into #B values(8, 'h')

insert into #A_B values (1, 1)
insert into #A_B values (1, 2)
insert into #A_B values (1, 3)
insert into #A_B values (1, 4)

desired results

foobar a b c d
Thanks again,

[Erland Sommarskog]

Guinness Mann (GM***@dublin.com) writes:

I know this is a self join, but I can't remember exactly how it goes.
Could someone help me out?

create table A
{
int idA,
varchar(30) dataA
}

For your next posting it would be appreciated if you actually run your
repro scripts in Query Analyzer, and fixed the syntax errors. This is
SQL, not C structs. :-)

Here a query that gives you the desired result. Notice that while you can
extend to more columns, the result set always has a fixed number of
columns, as those are the rules for SQL. For a completely dynamic result
set, you may look into RAC, www.rac4sql.net.

Anyway here is the query from hell:

SELECT A.dataA, B1.dataB, B2.dataB, B3.dataB, B4.dataB
FROM A
LEFT JOIN (A_B AB1 JOIN B B1 ON AB1.idB = B1.idB)
ON A.idA = AB1.idA
AND AB1.idB = (SELECT MIN(AB1a.idB)
FROM A_B AB1a
WHERE AB1a.idA = A.idA)
LEFT JOIN (A_B AB2 JOIN B B2 ON AB2.idB = B2.idB)
ON A.idA = AB2.idA
AND AB2.idB = (SELECT MIN(AB2a.idB)
FROM A_B AB2a
WHERE AB2a.idA = A.idA
AND AB2a.idB > AB1.idB)
LEFT JOIN (A_B AB3 JOIN B B3 ON AB3.idB = B3.idB)
ON A.idA = AB3.idA
AND AB3.idB = (SELECT MIN(AB3a.idB)
FROM A_B AB3a
WHERE AB3a.idA = A.idA
AND AB3a.idB > AB2.idB)
LEFT JOIN (A_B AB4 JOIN B B4 ON AB4.idB = B4.idB)
ON A.idA = AB4.idA
AND AB4.idB = (SELECT MIN(AB4a.idB)
FROM A_B AB4a
WHERE AB4a.idA = A.idA
AND AB4a.idB > AB3.idB)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Guinness Mann]

In article <Xn**********************@127.0.0.1>, so****@algonet.se
says...

For your next posting it would be appreciated if you actually run your
repro scripts in Query Analyzer, and fixed the syntax errors. This is
SQL, not C structs. :-)
I was in a hurry to post. <sigh> I did correct them in a follow-up
post.
Anyway here is the query from hell:

SELECT A.dataA, B1.dataB, B2.dataB, B3.dataB, B4.dataB
FROM A
LEFT JOIN (A_B AB1 JOIN B B1 ON AB1.idB = B1.idB)
ON A.idA = AB1.idA
AND AB1.idB = (SELECT MIN(AB1a.idB)
FROM A_B AB1a
WHERE AB1a.idA = A.idA)
LEFT JOIN (A_B AB2 JOIN B B2 ON AB2.idB = B2.idB)
ON A.idA = AB2.idA
AND AB2.idB = (SELECT MIN(AB2a.idB)
FROM A_B AB2a
WHERE AB2a.idA = A.idA
AND AB2a.idB > AB1.idB)
LEFT JOIN (A_B AB3 JOIN B B3 ON AB3.idB = B3.idB)
ON A.idA = AB3.idA
AND AB3.idB = (SELECT MIN(AB3a.idB)
FROM A_B AB3a
WHERE AB3a.idA = A.idA
AND AB3a.idB > AB2.idB)
LEFT JOIN (A_B AB4 JOIN B B4 ON AB4.idB = B4.idB)
ON A.idA = AB4.idA
AND AB4.idB = (SELECT MIN(AB4a.idB)
FROM A_B AB4a
WHERE AB4a.idA = A.idA
AND AB4a.idB > AB3.idB)

I was afraid it would look something like that. It almost makes me want
to restructure table A with four columns for the table B items.
Unfortunately, while the current solution always requires four columns,
future solutions may not, so I don't want to hardwire table A.

This query won't be run more than once or twice a day. Is there any
point in creating a stored procedure, or a view?

-- Rick

[Shervin Shapourian]

Well, depending on how big your actual problem is, you can use some other
solutions which may or may not be efficient. Like using a cursor to fill a
temporary table, or creating a dynamic script.

Shervin

"Guinness Mann" <GM***@dublin.com> wrote in message
news:MP************************@news.newsguy.com.. .

In article <Xn**********************@127.0.0.1>, so****@algonet.se
says...

For your next posting it would be appreciated if you actually run your
repro scripts in Query Analyzer, and fixed the syntax errors. This is
SQL, not C structs. :-)

I was in a hurry to post. <sigh> I did correct them in a follow-up
post.

Anyway here is the query from hell:

SELECT A.dataA, B1.dataB, B2.dataB, B3.dataB, B4.dataB
FROM A
LEFT JOIN (A_B AB1 JOIN B B1 ON AB1.idB = B1.idB)
ON A.idA = AB1.idA
AND AB1.idB = (SELECT MIN(AB1a.idB)
FROM A_B AB1a
WHERE AB1a.idA = A.idA)
LEFT JOIN (A_B AB2 JOIN B B2 ON AB2.idB = B2.idB)
ON A.idA = AB2.idA
AND AB2.idB = (SELECT MIN(AB2a.idB)
FROM A_B AB2a
WHERE AB2a.idA = A.idA
AND AB2a.idB > AB1.idB)
LEFT JOIN (A_B AB3 JOIN B B3 ON AB3.idB = B3.idB)
ON A.idA = AB3.idA
AND AB3.idB = (SELECT MIN(AB3a.idB)
FROM A_B AB3a
WHERE AB3a.idA = A.idA
AND AB3a.idB > AB2.idB)
LEFT JOIN (A_B AB4 JOIN B B4 ON AB4.idB = B4.idB)
ON A.idA = AB4.idA
AND AB4.idB = (SELECT MIN(AB4a.idB)
FROM A_B AB4a
WHERE AB4a.idA = A.idA
AND AB4a.idB > AB3.idB)

I was afraid it would look something like that. It almost makes me want
to restructure table A with four columns for the table B items.
Unfortunately, while the current solution always requires four columns,
future solutions may not, so I don't want to hardwire table A.

This query won't be run more than once or twice a day. Is there any
point in creating a stored procedure, or a view?

-- Rick

[Guinness Mann]

Ok, I'm still having trouble. Let's make this even simpler. Take this
table:

create table A_B
(
idA int,
idB int
)

Insert this data into it:

insert into A_B values (1, 1)
insert into A_B values (1, 2)
insert into A_B values (1, 3)
insert into A_B values (1, 4)
insert into A_B values (2, 5)
insert into A_B values (2, 6)
insert into A_B values (2, 7)
insert into A_B values (2, 8)
Now what I want out is

idA idB1 idB2 idB3 idB4
--- ---- ---- ---- ----
1 1 2 3 4
2 5 6 7 8

How would I structure the query?

-- Rick

[Erland Sommarskog]

Guinness Mann (GM***@dublin.com) writes:

I was afraid it would look something like that. It almost makes me want
to restructure table A with four columns for the table B items.
Unfortunately, while the current solution always requires four columns,
future solutions may not, so I don't want to hardwire table A.

This query won't be run more than once or twice a day. Is there any
point in creating a stored procedure, or a view?

Certainly it would be a good idea to package it in some way, in stored
procedure, a view or even in an ASP page. Would be a bit of work to
type in that monster all the time.

But I think you should look into change the design a bit. The below
still gives a complex query, but still one which is more straight
forward to write. The change I'm making is that I make the values
in the connection table A_B known to avoid those subqueries.

CREATE TABLE orders
(orderid int NOT NULL PRIMARY KEY,
somedata varchar(20) NOT NULL)

CREATE TABLE products (
productid int NOT NULL PRIMARY KEY,
productname varchar(23) NOT NULL)

CREATE TABLE orderdetails
(orderid int NOT NULL REFERENCES orders(orderid),
rowno tinyint NOT NULL CHECK (rowno BETWEEN 1 AND 4),
productid int NOT NULL REFERENCES products(productid),
PRIMARY KEY (orderid, rowno))
go
INSERT orders (orderid, somedata) VALUES (1, 'First order')
INSERT orders (orderid, somedata) VALUES (2, 'Second order')
INSERT products (productid, productname) VALUES (1, 'Airplane')
INSERT products (productid, productname) VALUES (2, 'Banana')
INSERT products (productid, productname) VALUES (3, 'Porridge')
INSERT products (productid, productname) VALUES (4, 'Stucko')
INSERT products (productid, productname) VALUES (5, 'Lightblub')
INSERT products (productid, productname) VALUES (6, 'Transistor')
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 1, 2)
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 2, 5)
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 3, 6)
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 4, 1)
INSERT orderdetails (orderid, rowno, productid) VALUES (2, 1, 4)
INSERT orderdetails (orderid, rowno, productid) VALUES (2, 2, 3)
go
SELECT o.somedata, p1.productname, p2.productname,
p3.productname, p4.productname
FROM orders o
LEFT JOIN (orderdetails od1
JOIN products p1 ON od1.productid = p1.productid)
ON o.orderid = od1.orderid
AND od1.rowno = 1
LEFT JOIN (orderdetails od2
JOIN products p2 ON od2.productid = p2.productid)
ON o.orderid = od2.orderid
AND od2.rowno = 2
LEFT JOIN (orderdetails od3
JOIN products p3 ON od3.productid = p3.productid)
ON o.orderid = od3.orderid
AND od3.rowno = 3
LEFT JOIN (orderdetails od4
JOIN products p4 ON od4.productid = p4.productid)
ON o.orderid = od4.orderid
AND od4.rowno = 4
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Guinness Mann]

In article <Xn********************@127.0.0.1>, so****@algonet.se says...

But I think you should look into change the design a bit. The below
still gives a complex query, but still one which is more straight
forward to write. The change I'm making is that I make the values
in the connection table A_B known to avoid those subqueries.

I see. Thank you. And if I needed to allow up to five, or six entries,
I would just add on another, or two more, sections. Very clever.

Thanks,

-- Rick

[Erland Sommarskog]

It occurred to me that the complex query can be even more simplfied
if you introduce a view:

CREATE VIEW orderproducts (orderid, rowno, productid, productname) AS
SELECT od.orderid, od.rowno, od.rowno, p.productname
FROM orderdetails od
JOIN products p ON od.productid = p.productid
go
SELECT o.somedata, op1.productname, op2.productname,
op3.productname, op4.productname
FROM orders o
LEFT JOIN orderproducts op1 ON o.orderid = op1.orderid
AND op1.rowno = 1
LEFT JOIN orderproducts op2 ON o.orderid = op2.orderid
AND op2.rowno = 2
LEFT JOIN orderproducts op3 ON o.orderid = op3.orderid
AND op3.rowno = 3
LEFT JOIN orderproducts op4 ON o.orderid = op4.orderid
AND op4.rowno = 4

It is not likely that this will matter for performance. The view only
serves the purpose to make the query easier to read and maintain.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Guinness Mann]

In article <Xn**********************@127.0.0.1>, so****@algonet.se
says...

It occurred to me that the complex query can be even more simplfied
if you introduce a view:

It is not likely that this will matter for performance. The view only
serves the purpose to make the query easier to read and maintain.

If one of your "performance" metrics is ease of maintenance, then the
view definitely aids performance.

Thanks again!

-- Rick

P.S. Although I could understand the query from hell and I even managed
to modify it for my particular situation, anyone who had to maintain the
code after I leave would have been overwhelmed. (Most likely) That's
not a good solution in my book. I always try to write code that seems
very easy to me, in the hopes that it will seem very easy to my
replacement.
[RF]