Query 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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2007-01-05 : 16:58:00

I have a query return following rows.
ID Type Count
1 New 2
1 Old 1
2 new 1
3 old 5
4 old 2
4 extra 3

I need result like this
ID New Old Extra
1 2 1 0
2 1 0 0
3 0 5 0
4 0 2 3

The type can be grow (now all rows has max three column it can be four or five or six for id 5 or 10 or 15)

How can i achive this

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 17:10:33

Can the number of types just keep on growing, or is the number fixed, but more than what you've given?

If it will keep on growing then you need a cross-tab query
See here for SQL Server 2000 http://sqlteam.com/item.asp?ItemID=2955
Or, see the PIVOT clause for SQL Server 2005

If the number of types is fixed then you can do this

SELECT ID,
    CASE WHEN Type = 'New' THEN Count ELSE 0 END AS [New],
    CASE WHEN Type = 'Old' THEN Count ELSE 0 END AS [Old],
    CASE WHEN Type = 'Extra' THEN Count ELSE 0 END AS [Extra]
FROM yourtable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-05 : 18:27:41

[code]SELECT ID,
SUM(CASE WHEN Type = 'New' THEN Count ELSE 0 END) AS [New],
SUM(CASE WHEN Type = 'Old' THEN Count ELSE 0 END) AS [Old],
SUM(CASE WHEN Type = 'Extra' THEN Count ELSE 0 END) AS [Extra]
FROM yourtable
GROUP BY ID, ORDER BY ID[/code]

Peter Larsson
Helsingborg, Sweden

drpkrupa
Yak Posting Veteran

74 Posts

Posted - 2007-01-05 : 23:45:06

The type can grow by itselt. How can i make dynamic query so if type get increse the column can increase too. I dont want a hard code. Need help please.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-06 : 03:27:09

quote:
Originally posted by drpkrupa

The type can grow by itselt. How can i make dynamic query so if type get increse the column can increase too. I dont want a hard code. Need help please.

If it will keep on growing then you need a cross-tab query
See here for SQL Server 2000 http://sqlteam.com/item.asp?ItemID=2955
Or, see the PIVOT clause for SQL Server 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources