Software

Ask something
Discussion Locked

Difficult Transact-SQL Statement

0 helpful
2 replies

by bgibilaro

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hey there,

This is a problem I simply have not been able to wrap my brain around. First, the basics, I am

running MS SQL Server 2000 on Windows XP Pro for the client and Windows 2000 Server for the

server.

So here is my problem, I havea table that looks something like this:

------------------------------------
FILE_NO   | DATE_TYPE   |  DATE    |
------------------------------------

123       | typeA       |  10/23/02|

456       | typeA       |  10/21/02|

456    | typeB       |  10/25/02|

789       | typeA       |  10/12/02|

789       | typeB       |  10/24/02|

789       | typeC       |  10/24/02|
------------------------------------

What I want to do is write a select statement that will return the information in this format:

---------------------------------------------
FILE_NO   | typeA   |  typeB    |   typeC   |
---------------------------------------------

123       | 10/23/02|  NULL     |   NULL    |

456       | 10/21/02|  10/25/02 |   NULL    |

789       | 10/12/02|  10/24/02 |   10/26/02|
---------------------------------------------

So basically I want to know the dates of each date type that exist for each file number. Keep in
mind that in reality, my table contains thousands of rows, so performance will be a concern here.

Any help you can offer here would be greatly appreciated.

2 replies

by feste the jester

try this

SELECT
FILE_NO,
Case
When TableName.DATE_TYPE = 'typeA' Then TableName.DATE
Else NULL
End
AS TYPEA,
Case
When TableName.DATE_TYPE = 'typeB' Then TableName.DATE
Else Null
End
AS TYPEB,
Case
When TableName.DATE_TYPE = 'typeC' Then TableName.DATE
Else NULL
End
AS TYPEC,

FROM
TABLENAME

by jeffreyt

I used your data and the following query. This assumes that there are only three values for DATE_TYPE - typeA, typeB and typeC. I also took the liberty of naming the table PIVOT and renaming the DATE column TDATE, as DATE is a reserved word.

Select A.FILE_NO, A.TDATE AS 'typeA', B.TDATE AS 'typeB', C.TDATE AS 'typeC'
From dbo.PIVOT AS A FULL OUTER JOIN dbo.PIVOT AS B on (A.FILE_NO = B.FILE_NO and A.DATE_TYPE = 'typeA' and B.DATE_TYPE = 'typeB') FULL OUTER JOIN dbo.PIVOT AS C ON (B.FILE_NO =C.FILE_NO and B.DATE_TYPE = 'typeB' and C.DATE_TYPE = 'typeC')
Where A.FILE_NO IS NOT NULL and
(A.DATE_TYPE = 'typeA' and A.TDATE IS NOT NULL) or
(B.DATE_TYPE = 'typeB' and B.TDATE IS NOT NULL) or
(C.DATE_TYPE = 'typeC' and C.TDATE IS NOT NULL)
Order by A.FILE_NO;

This discussion is closed to new replies.