T SQL 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 2000 Forums

SQL Server Development (2000)

T SQL help

Author

Topic

andooran
Starting Member

2 Posts

Posted - 2009-01-05 : 02:24:17

I have a table, Table1, that contains three columns
ID, IntValue1, TypeOfIntValue

Sample rows

ID IntValue1 TypeOfIntValue
1 20 Tax
1 220 Income
2 22 Tax
3 400 Income

TypeOfIntValue can be either "Tax" or "Income"

Each ID can have at most two rows of data - Tax/Income

Now I want to combine data for same ID into a single row. Given that I can only use SQL 2000 compliant T-SQL, how do I write this query? (Cannot use Pivot operation etc.)

Expected result is like this

ID Tax Income
1 20 220
2 22 NULL
3 NULL 400

I dont want the exact query but would appreciate any guidance like suggestions to use certain type of query etc.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 02:34:59

SELECT ID,
MAX(CASE WHEN TypeOfIntValue = 'Income' THEN Intvalue ELSE NULL END) AS Income,
MAX(CASE WHEN TypeOfIntValue = 'Tax' THEN Intvalue ELSE NULL END) AS Tax
FROM Table1
GROUP BY ID
ORDER BY ID

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources