Dumb query question - 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 2000 Forums

SQL Server Development (2000)

Dumb query question

Author

Topic

leifthoreson
Starting Member

16 Posts

Posted - 2004-02-09 : 14:54:21

I have a table that contains information that looks like this

col1 col2 col3
shift 1 10/22/2004
shift 2 10/23/2004
sample 455 10/22/2004
sample 545 10/24/2004
mold 1 10/22/2004

..
I amy trying to write a query statment that would output this date to look something like

col3 shift mold sample
10/22/2004 1 1 455
10/23/2004 2
10/24/2004 545

At first I thought of using a case statment , but the issue I have is that the values in col3 are not a fixed set. the user can add new "defenitions" that will be assigned the value contained in col2 and I need the query to return columns named after distinct values fould in col1.
That and I need to combine the rows that have the same col3
I'm new to sql ... not sure how I should go about this.
Thanks
Leif

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-09 : 16:27:27

If you get more than 1 value per date, you get dup rows....

CREATE TABLE myTable99(Col1 varchar(10), Col2 int, Col3 datetime)
GO

INSERT INTO myTable99(Col1, Col2, Col3)
SELECT 'shift',	   1, '10/22/2004' UNION ALL
SELECT 'shift',    2, '10/23/2004' UNION ALL
SELECT 'sample', 455, '10/22/2004' UNION ALL
SELECT 'sample', 545, '10/24/2004' UNION ALL
SELECT 'mold',     1, '10/22/2004'
GO

    SELECT A.Col3, B.Col2 AS shift, C.Col2 AS mold, D.Col2 AS sample
      FROM (SELECT DISTINCT Col3 FROM myTable99) AS A
 LEFT JOIN myTable99 B
	ON A.Col3 = B.Col3
       AND B.Col1 = 'Shift'
 LEFT JOIN myTable99 C
	ON A.Col3 = C.Col3
       AND C.Col1 = 'Mold'
 LEFT JOIN myTable99 D
	ON A.Col3 = D.Col3
       AND D.Col1 = 'Sample'
 GO

DROP TABLE myTable99
GO