Hi,Need help on sql select statement structure

What you are wishing to do is officially called Pivoting, you want to "swing" the data from the vertical to the horizontal so to speek.

You can use TSQL's CASE function in your select statement to do that.

SELECT 
     date,
     SUM(CASE WHEN source = 'star' THEN 1 ELSE 0 END) AS star,
     SUM(CASE WHEN source = 'newstraits' THEN 1 ELSE 0 END) AS newstraits,
     SUM(CASE WHEN source = 'mlymail' THEN 1 ELSE 0 END) AS mlymail
FROM
     table
GROUP BY 
     date,
     source

Hi hollystyles,

Thanx for the reply.it works great.But I have some columns in the table where the values are unpredictable,which means that it may vary. Even the field source might include some other values which the caller input. How can i create the sql if this is the case because from the sql i tried,it seems that we are hardcoding the sql

e.g "CASE WHEN source = 'star' " because we know one of the value in the column is star. what if the value is not not from the dropdownlist,i mean the value stored is from a text box which means the user can input any string.

Can u help again pls. Thanx you for the quick reply.

yes we are hard coding, if we know the set of types, the better performance is worth the hardcoding. As this is not the case for you the next thing you need to look at is dynamic SQL, where you concatenate your select statement in a string variable at runtime and execute it.

Consider this TSQL:

declare @sql varchar(1000)
declare @table varchar(32)

select @table = 'sometable'
select @sql = 'select * from ' + @table

exec(@sql)

There are some fantastic articles on this and how to use it to pivot data, far better than anything I could do justice in this thread so here is a link to SqlTeam.com a great resource for MS SQL Server stuff I recommend you sign up, I post there as CAS_O. (though not for some time)

http://www.sqlteam.com/item.asp?ItemID=2955

On last word of warning, there is a limit to how many columns you can pivot, you cannot declare varchar(infinite) and the fact you want to pivot free text is an alarm bell, you need to consider swapping your free text with a drop down select. Consider:

user 1 : star
user 2 : stra (oops!)

Hi Hollystyles,

You have been a great help. Thanx alot. I'll sign up to the forum you suggested. Can u explain about the example you gave in the last post. I dont really understand the example you gave regarding the user 1 and user two.

Hey no problem, if you click the link that says 'Add to hollystyles's reputation' that would be nice.

The user1 user2 thing was about validating input from the user of your application. If you are going to allow the user to type anything in the 'source' column, you are gong to get hundreds of different variations of the same thing, mostly user typos and your pivot report is going to stretch miles off the right side of your screen. eventually you will hit the limit of how long your SQl select can be and your program will error yuck!

A golden rule is you should NEVER trust input from ANY source ALWAYS restrict choice (even on free text you should limit it's allowed length as an absolute minimum) AND validate it before accepting it.

I would recomend you have a drop down list box with:

star
newstraits
mlymail
other

provide a seperate free text for when 'other' is chosen and put that in a different column, say 'source_other'

This way YOU control how 'WIDE' your pivot report gets (you control the dropdown list) NOT the user.