•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 392,071 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,238 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 5125 | Replies: 6
 |
•
•
Join Date: Jul 2006
Posts: 15
Reputation: 
Rep Power: 3
Solved Threads: 0
Newbie Poster
Hi,Need help on sql select statement structure Hi,
I am not sure if my question sounds silly. I have a table that has a few columns like date,source,race and state. I need to generate a report. The table stores the details of callers including name,age,sex. But the report need to display in this format counting the number of calls for each state,each source and race distinctly.
source state race
star sel ind
newstraits KL malay
mlymail pahang china
i need to display the report as
date star newstraits mlymail sel Kl pahang ind malay china
10/2/2006 2 3 4 1 2 4 5 6
I'm not sure if this can be done because what i need to do seems like getting the values of a column and displaying it as a new column.
I hope my question is clear.
i Hope someone can,if you dun find this question clear,please reply.
Thnk you in advance.
I am not sure if my question sounds silly. I have a table that has a few columns like date,source,race and state. I need to generate a report. The table stores the details of callers including name,age,sex. But the report need to display in this format counting the number of calls for each state,each source and race distinctly.
source state race
star sel ind
newstraits KL malay
mlymail pahang china
i need to display the report as
date star newstraits mlymail sel Kl pahang ind malay china
10/2/2006 2 3 4 1 2 4 5 6
I'm not sure if this can be done because what i need to do seems like getting the values of a column and displaying it as a new column.
I hope my question is clear.
i Hope someone can,if you dun find this question clear,please reply.
Thnk you in advance.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,164
Reputation:
Rep Power: 7
Solved Threads: 58
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.
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
•
•
Join Date: Jul 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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.
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.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,164
Reputation:
Rep Power: 7
Solved Threads: 58
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:
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
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
Last edited by hollystyles : Jul 18th, 2006 at 4:00 am.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,164
Reputation:
Rep Power: 7
Solved Threads: 58
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!)
user 1 : star
user 2 : stra (oops!)
•
•
Join Date: Jul 2006
Posts: 15
Reputation:
Rep Power: 3
Solved Threads: 0
Newbie Poster
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.
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.
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,164
Reputation:
Rep Power: 7
Solved Threads: 58
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.
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.
Last edited by hollystyles : Jul 18th, 2006 at 4:51 am.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
- SQL select statement help (MS Access and FileMaker Pro)
- cfselect issue trying to get results from a sql select statement (ColdFusion)
- Program Problem with a select statement to access Data base (C)
Other Threads in the MS SQL Forum
- Previous Thread: select rows divisible by 5
- Next Thread: SQL 2005 won't start after critical update reboot
