473,883 Members | 1,741 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Rows as Columns

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
Jul 20 '05 #1
9 7743
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,

Jul 20 '05 #2
Guinness Mann (GM***@dublin.c om) 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
Jul 20 '05 #3
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
Jul 20 '05 #4
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.c om> 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

Jul 20 '05 #5
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

Jul 20 '05 #6
Guinness Mann (GM***@dublin.c om) 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(produc tid),
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
Jul 20 '05 #7
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

Jul 20 '05 #8
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
Jul 20 '05 #9
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 "performanc e" 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]
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2556
by: Diego TERCERO | last post by:
Hi... I'm working on a tool for editing text resources for a family of software product my company produces. These text resources are found in a SQL Server database, in a table called "Resource" with the following structure : Resource{,en,fr,es} Yes.. these are the only languages supported actually. A couple of rows in that table would look like this :
0
4684
by: Ireneus Broncel | last post by:
I have a class which reads Groups and Users from ActiveDirectory. The Problem is, that i have about 10000 rows as product. When I am trying to read the "memberOf" Objects out of this field i get allways different count of rows. If anybody knows something about this kind of problem, I would appreciate any help. Thx.
3
4895
by: Jim Heavey | last post by:
Trying to figure out the technique which should be used to add rows to a datagrid. I am thinking that I would want an "Add" button on the footer, but I am not quite sure how to do that. Is that the best method? Do you have a sample of how to do this?
1
18555
by: Chris Smith | last post by:
Experience Posters, Sorry if this is not the right group to post this question. He is my issue; Is there a way without the use of 3rd party controls, to transpose the rows of a datatable to columns of the datatable? Any help would be great.
68
6854
by: Martin Joergensen | last post by:
Hi, I have some files which has the following content: 0 0 0 0 0 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 1 1 1 1 0 0 0 0 0 0 0
1
19804
by: Lars E | last post by:
Hi all I have a small problem. I have a datatable with 8 columns. But it is only data in 5 of the columns. Data for the remaing 3 columns is in another dataset. I Want to run trough the datatable and fill out the remaining data. My code so far: if (this.fetch("custinfo", "fetchCustInfo", out customers, parameters))
0
6194
by: sysmanint1 | last post by:
I am a total neophyte at Visual Basic but found the following post and reply from Clint concerning a dynamic range. Also, have never "posted" to a discussion I have made a macro that works on a template spreadsheet with a fixed number of columns to be "macro'd" bounded by data that changes in its number of rows. That is, the bounding data is imported to the spreadsheet before the calculated columns are run. But the rows of this data...
7
15016
by: lethek39 | last post by:
Hey I have been trying to figure out how to sum rows and columns in a matrix square. I also have been trying to get the program to list the numbers of the diagonal in the matrix. So far this is the code I have (I am using Python): def generate (rows, cols): # This just prints the coordinates and the number that goes in it, It also prints the matrix in a square import random m = {} for r in range(rows): for c in...
5
2798
by: Sieldan | last post by:
I'm trying to throw some data around so that I can manipulate it more easily. Unfortunately, I don't really know what I'm doing. :) So I'm creeping my way through it step by step. What I have done so far is find the rows of data I need to work with, copied them into a temporary table, and removed them from the original table. Now I am trying to send the manipulated rows back to the original table. The hitch is the original table has two...
1
7315
by: tucson | last post by:
I have a gridview that has a blank row with 2 input fields: file to upload, and a description of the file. They click Add and a new row is added, Remove and the row is removed. The problem is: When a new row is added, I loop through the existing gridview rows, store the data in a dataset, and rebind. In debug mode, I see the values I entered but when I rebind, it's not displayed in the gridview. Here's the code aspx code: ...
0
11153
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10757
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10420
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9583
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4620
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4225
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3239
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.