Our database is normalized to the point that I need to build a dataset to
render a report from.
For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and 15
rows of county names. I need them to be in 1 row and 15 columns to make up
the data going accross the report. What is the best way to do this?
Thanks,
ShawnI you create a matrix report with County Name across the top of the matrix,
it will automatically generate the appropriate number of columns...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:55F38FB8-CBD1-4C1C-9611-8247A1A5C60E@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from.
> For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
> (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and
15
> rows of county names. I need them to be in 1 row and 15 columns to make
up
> the data going accross the report. What is the best way to do this?
> Thanks,
> Shawn
>|||I need to make a report that looks like...
Statistic A Statistic B Statistic C Statistic D
----
99 07 102 91
It would be easy but the values are all in one column in the table, like...
KeyValue | StatisticCode | StatisticValue
001| A| 99
002| B| 07
003| D| 91
004| C| 102
Thanks,
Shawn
"Wayne Snyder" wrote:
> I you create a matrix report with County Name across the top of the matrix,
> it will automatically generate the appropriate number of columns...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:55F38FB8-CBD1-4C1C-9611-8247A1A5C60E@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from.
> >
> > For example "select county name from COUNTYTABLE where COUNTYNUMBER IN
> > (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15) ". This query return 1 column and
> 15
> > rows of county names. I need them to be in 1 row and 15 columns to make
> up
> > the data going accross the report. What is the best way to do this?
> >
> > Thanks,
> > Shawn
> >
>
>
Showing posts with label county. Show all posts
Showing posts with label county. Show all posts
Monday, March 19, 2012
Wednesday, March 7, 2012
How to use CTE
I use CTE of VS2005:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?
After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan
|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
??:1131894130.133010.143350@.g44g2000cwa.googl egroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>
|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> ??:1131894130.133010.143350@.g44g2000cwa.googl egroups.com...
>
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?
After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan
|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
??:1131894130.133010.143350@.g44g2000cwa.googl egroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>
|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> ??:1131894130.133010.143350@.g44g2000cwa.googl egroups.com...
>
How to use CTE
I use CTE of VS2005:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
'?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
>
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
'?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
>
How to use CTE
I use CTE of VS2005:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
'?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
>> After defining the CTE, you should use it in another SQL statement.
>> Try something like this:
>> WITH AA (County,Town ) AS
>> (
>> SELECT County,Town
>> FROM Zip where zip='413'
>> )
>> SELECT * FROM aa
>> Razvan
>
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
But it result an Error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.
What's the problems?
How can I do ?After defining the CTE, you should use it in another SQL statement.
Try something like this:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
SELECT * FROM aa
Razvan|||Thanks,
It can run.
Is the CTE table can be used only once?
It fail if I add tow statement after defing the CTE:
WITH AA (County,Town ) AS
(
SELECT County,Town
FROM Zip where zip='413'
)
update AA SET County ='another'
Select * from AA
"Razvan Socol" <rsocol@.gmail.com>
'?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
> After defining the CTE, you should use it in another SQL statement.
> Try something like this:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> SELECT * FROM aa
> Razvan
>|||> Is the CTE table can be used only once?
Yes, the execution scope of a CTE is a single SELECT, INSERT, UPDATE, or
DELETE statement.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:epsTOlI6FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> It can run.
> Is the CTE table can be used only once?
> It fail if I add tow statement after defing the CTE:
> WITH AA (County,Town ) AS
> (
> SELECT County,Town
> FROM Zip where zip='413'
> )
> update AA SET County ='another'
> Select * from AA
>
>
> "Razvan Socol" <rsocol@.gmail.com>
> '?:1131894130.133010.143350@.g44g2000cwa.googlegroups.com...
>> After defining the CTE, you should use it in another SQL statement.
>> Try something like this:
>> WITH AA (County,Town ) AS
>> (
>> SELECT County,Town
>> FROM Zip where zip='413'
>> )
>> SELECT * FROM aa
>> Razvan
>
Subscribe to:
Posts (Atom)