select statement combining rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select statement combining rows

Author

Topic

kbsimm
Starting Member

5 Posts

Posted - 2008-03-30 : 14:57:15

I need some help with a query . I have two tables "config" and "item".

I have table config as follows:
ConfigID
ItemID

With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112

And table item as follows:
ItemID
ItemTypeID
ItemValue

With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789

So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID

Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL

How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC

Thanks for your help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 15:02:53

[code]
SELECT ConfigID,
case when ItemTypeID = '1' then i.ItemValue end AS Model,
case when ItemTypeID = '3' then i.ItemValue end AS Minor,
case when ItemTypeID = '2' then i.ItemValue end AS Customer
FROM config c
join item i c.ItemID = i.ItemID
WHERE ConfigID = '14583'
[/code]

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kbsimm
Starting Member

5 Posts

Posted - 2008-03-30 : 15:30:56

Thanks Spirit1...I think you are missing "ON" in the From clause.

But... I tried this and still got:

ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 15:54:00

true. missed an on. try this:


SELECT ConfigID,
MAX(case when ItemTypeID = '1' then i.ItemValue end) AS Model,
MAX(case when ItemTypeID = '3' then i.ItemValue end) AS Minor,
MAX(case when ItemTypeID = '2' then i.ItemValue end) AS Customer 
FROM config c
     join item i ON c.ItemID = i.ItemID
WHERE ConfigID = '14583'
GROUP BY ConfigID

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kbsimm
Starting Member

5 Posts

Posted - 2008-03-30 : 21:08:55

Thanks Spirit1 that worked great....

Now....
It is part of a larger query such as

SELECT ST.ProdctNo
C.ConfigID
FROM SomeTable ST, Config C
WHERE ST.ConfigID = C.ConfigID

Now instead of C.ConfigID I need Model, Minor, Customer....

I know it's a little more abstract but the actual query is a bit long.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-31 : 04:27:43

please use proper join syntax for writing queries.
you can join to your table to get other data:


select ...
from config c1 -- you need this if you must get any other data from config table
     join
     (
     SELECT ConfigID,
     MAX(case when ItemTypeID = '1' then i.ItemValue end) AS Model,
     MAX(case when ItemTypeID = '3' then i.ItemValue end) AS Minor,
     MAX(case when ItemTypeID = '2' then i.ItemValue end) AS Customer 
     FROM config c
          join item i ON c.ItemID = i.ItemID
     WHERE ConfigID = '14583'
     GROUP BY ConfigID
     ) c2 on c1.ConfigID = c2.ConfigID
     join SomeTable ST on ST.ConfigID = C2.ConfigID

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

kbsimm
Starting Member

5 Posts

Posted - 2008-03-31 : 10:16:36

Thanks Spirit1....

I got it to work last night by using
Select...
FROM config c1,
SomeTable ST,
(myselect) AS C2
WHERE ST.ConfigID = c1.ConfigID
AND c1.ConfigID = c2.ConfigID

Question 1) by "please use proper join syntax for writing queries" do you mean always use
FROM table1 t1 JOIN table2 t2 ON t1.row = t2.row
rather then
FROM table1 t1,
table2 t2
WHERE t1.row = t2.row

Question 2)
I used "AS" last night which I more associate with variable aliasing which worked but today I see that dropping the "AS" is more appropriate for table aliasing. But I am suprized that "AS" worked as I would think in "AS t2" it would assign t2 as a variable not a temp table. Or rather try to and fail.

thanks again for the lessons....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources