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.
| Author |
Topic |
|
M8KWR
Starting Member
26 Posts |
 Posted - 2007-07-27 : 07:49:34
|
| I have got a table with 4 columns,USERID - Row HeadingDBNAME - Row HeadingACTVCODE - Row HeadingRESULTCODE - Column Heading + Count of column headingI am trying to create a crosstab type query within Visual Studio 2005, with SQL 2000.I can do this within Access and crystal reports, but would prefer to create a table type of view without the use of crystal reports.is this possible??Many thanks for any help in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-27 : 07:53:07
|
SELECT UserID, DbName, ActvCode,SUM(CASE WHEN ResultCode = 'A' THEN 1 ELSE 0 END) AS [A],SUM(CASE WHEN ResultCode = 'B' THEN 1 ELSE 0 END) AS [B],SUM(CASE WHEN ResultCode = 'C' THEN 1 ELSE 0 END) AS [C],...FROM Table1GROUP BY UserID, DbName, ActvCodeORDER BY UserID, DbName, ActvCode
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-27 : 08:39:40
|
| If you are using Crystal Reports to present this, you are much better off doing the cross-tab there, it is shorter and more efficient, especially the T-SQL portion. The technique Peso showed works, but only if you can hard-code in the column names ahead of time. My advice is generally this: you have a tool that will take normalized data and present it in a cross-tab format, leave the data as is and let the tool do the formatting.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
M8KWR
Starting Member
26 Posts |
Posted - 2007-07-27 : 08:57:21
|
| i should be able to write the sql statement with VS to get the only the information that i require, as i will be able to get the list of RESULTCODE before i call this,thanks for this. |
|
|
|
|
|
|
|
|
Creating a CrossTab Query