2 simple queries HELP - SQL Server Forums

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)

2 simple queries HELP

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-11-06 : 14:00:46

Hi

QUERY 1

I have a table with data of price of products from 9 different suplliers here I will use only 4 to reduce data. I need to get only those products where 2 or more suppliers have given me a price.
I do not want where 8 suppliers are null.

Product_ID, Supplier1, Supplier2, Supplier3, Supplier4
'AA', 100.00, NULL, NULL, NULL
'BB', 200, 199, NULL, NULL
'CC', 400, NULL, NULL, 380
'DD', NULL, NULL, 100, NULL
'EE', NULL, 100, 101, 103
'FF', 400, 405, 409, 408
'GG', NULL, NULL, NULL, NULL

this query should also work if the number of suppliers increase from 9 to 10 or 11 or even more. Is there a way of doing this. In this case all rows should be returned but with Product_ID AA, DD and GG

QUERY 2
I have another table table

ID DAY

1 Monday
2 Tuesday
3 Wednesday
4 THursday
5 Friday

how do I make the rows to columns and get a table like and what is this process called.

DAY Monday Tuesday Wednesday Thursday Friday
ID 1 2 3 4 5

-----------------------------------------------------------------------------------------------
Ashley Rhodes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 14:04:31

PIVOT or CROSSTAB report.

E 12°55'05.25"
N 56°04'39.16"