By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,143 Members | 865 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,143 IT Pros & Developers. It's quick & easy.

Difficult SQL Query with multiple records detail

P: n/a
I am trying to turn a list of data that looks like this:
ID A B C
---- ---- ---- ----
1 0 1 3
2 1 2 1

Into a result set that looks like this on a report:
ID Product Qty
---- ----------- -----
1 B 1
1 B 3
2 A 1
2 A 2
2 A 1

Is this possible? If so, how would I do it? I would prefer not having
to use VB, I would rather solve this in SQL/Queries alone.

Using Access 2000 or SQL Server 2000

Marco

Dec 1 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try this ...

SELECT tblData.ID, 'A' AS Type, [A] As Value
UNION
SELECT tblData.ID, 'B' AS Type, [b] As Value
UNION
SELECT tblData.ID, 'C' AS Type, [C] As Value
ORDER BY ID, Type, Value

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Flash" <ma***********@merial.com> wrote ...
I am trying to turn a list of data that looks like this:
ID A B C
---- ---- ---- ----
1 0 1 3
2 1 2 1

Into a result set that looks like this on a report:
ID Product Qty
---- ----------- -----
1 B 1
1 B 3
2 A 1
2 A 2
2 A 1

Is this possible? If so, how would I do it? I would prefer not having
to use VB, I would rather solve this in SQL/Queries alone.

Using Access 2000 or SQL Server 2000

Marco

Dec 1 '05 #2

P: n/a
Actually, this is more like it ...

SELECT tblData.ID, 'A' AS Product, tblData.[A] As Qty
UNION
SELECT tblData.ID, 'B' AS Product, tblData.[b] As Qty
UNION
SELECT tblData.ID, 'C' AS Product, tblData.[C] As Qty
ORDER BY ID, Product, Qty

--
Dec 1 '05 #3

P: n/a
Thank you
That's what I was looking for.

Danny J. Lesandrini wrote:
Actually, this is more like it ...

SELECT tblData.ID, 'A' AS Product, tblData.[A] As Qty
UNION
SELECT tblData.ID, 'B' AS Product, tblData.[b] As Qty
UNION
SELECT tblData.ID, 'C' AS Product, tblData.[C] As Qty
ORDER BY ID, Product, Qty

--


Dec 1 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.