Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

computed columns

i need some info about when a computed column is recalculated. i am using a
udf and return value depends on columns from other tables. what if these
columns are updated ?
is the computed columns updated too then?
thanksNon-indexed computed columns are calculated whenever they are accessed.
Indexed computed columns are re-calculated whenever data is updated or
inserted.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127815998.052029.86950@.g44g2000cwa.googlegroups.com...
> Non-indexed computed columns are calculated >whenever they are accessed.
thanks

> Indexed computed columns are re-calculated whenever >data is updated or
> inserted.
is it re-calculated when the data in the other tables that the computed
column depends on updated too?

> --
> David Portas
> SQL Server MVP
> --
>|||As I understand it you want to use a computed column based on a scalar
UDF that references other tables. Such a column would not qualify to be
part of an index since the function would be non-deterministic. The
computed column would therefore behave like any other non-indexed
computed column: it would be calculated every time the computed value
is accessed in a query or other data retrieval operation, NOT when
dependent data is updated (whether or not that data is in other
tables).
In my opinion this would be an unwise use of a computed column. The
main use-case that I can think of for computed columns is to index an
expression that wouldn't otherwise be valid in an indexed view. Since
this doesn't apply to your case I think it would be a mistake to use a
computed column, especially since a join in a view is almost certainly
much more efficient than retirieving data through a scalar function.
Views are the place to denormalize your data.
David Portas
SQL Server MVP
--|||i got it now, thanks so much for the great info!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127818936.527212.196370@.o13g2000cwo.googlegroups.com...
> As I understand it you want to use a computed column based on a scalar
> UDF that references other tables. Such a column would not qualify to be
> part of an index since the function would be non-deterministic. The
> computed column would therefore behave like any other non-indexed
> computed column: it would be calculated every time the computed value
> is accessed in a query or other data retrieval operation, NOT when
> dependent data is updated (whether or not that data is in other
> tables).
> In my opinion this would be an unwise use of a computed column. The
> main use-case that I can think of for computed columns is to index an
> expression that wouldn't otherwise be valid in an indexed view. Since
> this doesn't apply to your case I think it would be a mistake to use a
> computed column, especially since a join in a view is almost certainly
> much more efficient than retirieving data through a scalar function.
> Views are the place to denormalize your data.
> --
> David Portas
> SQL Server MVP
> --
>

Computed Column

Is there a query that can be run to return all the tables in a database that
contain a computed column?
We are running SQL 2005, SP1, on Windows 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200612/1
SELECT DISTINCT object_name(object_id)
FROM sys.computed_columns
Roy Harvey
Beacon Falls, CT
On Fri, 01 Dec 2006 21:38:16 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>Is there a query that can be run to return all the tables in a database that
>contain a computed column?
>We are running SQL 2005, SP1, on Windows 2003.

Computed column

Hi,
I am trying yo return a set or records by using a SELECT statement, the
table contains a column called CreationDate.
I want to add one more column to the resulting dataset (not to the table),
that value of the column will be 1 if the CreationDate is 1 day before the
current time, 0 otherwise.
Any idea how to do it?
thanks first,
Gnicuse a case statement.
SELECT 'return' = case
WHEN datediff > 0 THEN 1
ELSE 0
FROM table|||I tried the statement and it returns
"Incorrect syntax near the keyword 'FROM'."
So it doesn't work, it that a Yukon feature?
Because I am using SQL Server 2000
thanks
"Red2" <sdibello@.gmail.com> wrote in message
news:1142952098.221443.242870@.i39g2000cwa.googlegroups.com...
> use a case statement.
> SELECT 'return' = case
> WHEN datediff > 0 THEN 1
> ELSE 0
> FROM table
>|||Oh I found that I need to add an END keyword at the end the case statement.
It works now.
thanks for your help
Gnic
"Gnic" <gasnic@.gmail.com> wrote in message
news:e4rrd8QTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I tried the statement and it returns
> "Incorrect syntax near the keyword 'FROM'."
> So it doesn't work, it that a Yukon feature?
> Because I am using SQL Server 2000
> thanks
>
> "Red2" <sdibello@.gmail.com> wrote in message
> news:1142952098.221443.242870@.i39g2000cwa.googlegroups.com...
>sqlsql

Computed Column

Is there a query that can be run to return all the tables in a database that
contain a computed column?
We are running SQL 2005, SP1, on Windows 2003.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200612/1SELECT DISTINCT object_name(object_id)
FROM sys.computed_columns
Roy Harvey
Beacon Falls, CT
On Fri, 01 Dec 2006 21:38:16 GMT, "cbrichards via droptable.com"
<u3288@.uwe> wrote:

>Is there a query that can be run to return all the tables in a database tha
t
>contain a computed column?
>We are running SQL 2005, SP1, on Windows 2003.

Computed column

Hi,
I am trying yo return a set or records by using a SELECT statement, the
table contains a column called CreationDate.
I want to add one more column to the resulting dataset (not to the table),
that value of the column will be 1 if the CreationDate is 1 day before the
current time, 0 otherwise.
Any idea how to do it?
thanks first,
Gnic
use a case statement.
SELECT 'return' = case
WHEN datediff > 0 THEN 1
ELSE 0
FROM table
|||I tried the statement and it returns
"Incorrect syntax near the keyword 'FROM'."
So it doesn't work, it that a Yukon feature?
Because I am using SQL Server 2000
thanks
"Red2" <sdibello@.gmail.com> wrote in message
news:1142952098.221443.242870@.i39g2000cwa.googlegr oups.com...
> use a case statement.
> SELECT 'return' = case
> WHEN datediff > 0 THEN 1
> ELSE 0
> FROM table
>
|||Oh I found that I need to add an END keyword at the end the case statement.
It works now.
thanks for your help
Gnic
"Gnic" <gasnic@.gmail.com> wrote in message
news:e4rrd8QTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I tried the statement and it returns
> "Incorrect syntax near the keyword 'FROM'."
> So it doesn't work, it that a Yukon feature?
> Because I am using SQL Server 2000
> thanks
>
> "Red2" <sdibello@.gmail.com> wrote in message
> news:1142952098.221443.242870@.i39g2000cwa.googlegr oups.com...
>
sqlsql

Computed Column

Is there a query that can be run to return all the tables in a database that
contain a computed column?
We are running SQL 2005, SP1, on Windows 2003.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200612/1SELECT DISTINCT object_name(object_id)
FROM sys.computed_columns
Roy Harvey
Beacon Falls, CT
On Fri, 01 Dec 2006 21:38:16 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>Is there a query that can be run to return all the tables in a database that
>contain a computed column?
>We are running SQL 2005, SP1, on Windows 2003.

complicated query

I have a table which has 4 columns
Sales_person, vehicle_id, price, date
I want a query to return names of the sales persons who sold the
vehicle at a record price in the month of may 2006.
say if sales person A sold a car for 25k in April 06 and 26k in may 06
I want the record high price for all sales if occured in may 06. But if
B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec
2005 I do not want that record.
any help with this query.I'm just guessing at what you want, but here are two
possibilities.
Since there is only one record high price, maybe you
want just want all sales at the record high price that
happened to occur in May, 2005:
select
Sales_person, Vehicle_id, price, date
from T
where
date >= '20050501'
and date < '20050601'
and price = (
select max(price)
from T
)
Your explanation is more complicated, but I can't figure
out what else it might mean.
Steve Kass
Drew University
http://www.stevekass.com
VJ wrote:

>I have a table which has 4 columns
>Sales_person, vehicle_id, price, date
>I want a query to return names of the sales persons who sold the
>vehicle at a record price in the month of may 2006.
>say if sales person A sold a car for 25k in April 06 and 26k in may 06
>I want the record high price for all sales if occured in may 06. But if
>B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec
>2005 I do not want that record.
>any help with this query.
>
>|||Look into datepart and max
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1149533614.614289.111110@.h76g2000cwa.googlegroups.com...
>I have a table which has 4 columns
> Sales_person, vehicle_id, price, date
> I want a query to return names of the sales persons who sold the
> vehicle at a record price in the month of may 2006.
> say if sales person A sold a car for 25k in April 06 and 26k in may 06
> I want the record high price for all sales if occured in may 06. But if
> B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec
> 2005 I do not want that record.
> any help with this query.
>

Complex SELECT QUERY using look-up tables

Hi gang,

I'm trying to find the best way to get a SELECT query to return field values for a table that are stored in another lookup table. Here's a basic example that will illustrate what I'm trying to do.

Assume three tables: tblItem, tblCustomFieldNames, tblCustomFieldValues.

The schemas/columns for the tables are as follows:

tblItem
id
itemName

tblCustomFieldName
id
customFieldName

tblCustomFieldValue
id
customFieldValue
customFieldID
itemID references tblItem(id)

Further assume, that the tables contain the following data:

tblItem
id|itemName
(1,CPU)
(2,Motherboard)

tblCustomFieldName
id|customFieldName
(1,Manufacturer)
(2,Price)
(3,Qty)

tblCustomFieldValue
id|customFieldValue|customFieldID|itemID
(1,AMD,1,1)
(2,$99,2,1)
(3,2,3,1)
(4,ASUS,1,2)
(5,$79,2,2)
(6,1,3,2)

My question is what does my SQL "SELECT query" syntax need to be such that I am able to return a result with the following form:

tblItem.id|Manufacturer|Price|Qty
(1, Intel, $99, 2)
(2, ASUS, $79, 1)

Note: It's not an option for me to re-design the database schema, as it's someone else's database. I simply need to be able to obtain the above resultset using a single SELECT query.

Thanks in advance! :)
-EHere you are:SELECT id, MAX(manufacturer) manufacturer, MAX(price) price, MAX(qty) qty
FROM
(
SELECT i.id,
CASE n.id
WHEN 1 THEN v.customfieldvalue
ELSE NULL
END manufacturer ,
CASE n.id
WHEN 2 THEN v.customfieldvalue
ELSE NULL
END price,
CASE n.id
WHEN 3 THEN v.customfieldvalue
ELSE NULL
END qty
FROM TBLITEM i, TBLCUSTOMFIELDNAME n, TBLCUSTOMFIELDVALUE v
WHERE v.customfieldid = n.id
AND i.id = v.itemid
)
GROUP BY id;

Complex query, whats the best way?

I have a query that must return information for an invoice. It is not straight forward though. Some things need to be displayed from the if something else is happening...

For example...

declare @.idintdeclare @.chargeidintset @.id = 1set @.chargeid = 9declare @.ppbit, @.pagesint, @.pagefeemoney, @.pagechargemoneyset @.pp = (select perpagefrom tblmainfeeswhere mainfeesid = @.chargeid)if (@.pp = 1)beginset @.pages = (select pagesfrom tblchargesinnerjoin tblrequeston requestid = fkrequestidwhere requestid = @.id)if (@.pages > 0)beginset @.pagefee = (select perpagefeefrom tblmainfeeswhere mainfeesid = @.chargeid)set @.pagecharge = (@.pages * @.pagefee)endend...select @.pagechargeas PerPageCharge

Or should I do some complex query with only one select?

Its hard for anyone to understand if your question is like this: "Some things need to be displayed from the if something else is happening..."

Give us some sample data and your query and what you are expecting to see out of it.

|||

Your right, sorry about that. I slid that one in quick before a meeting...

I will try to explain better.

I have stored procedure that returns data for an invoice, and because Ihate Crystal Reports, I do the most work I can in the stored procedure (formating the text, etc...).

The current query I have involves a 2 complex queries with tons of nested case statements. It is hard to read and does not flow nicely at all, but it works.

I just made enough changes to the database that the stored procedure needs to be rewritten.

I took this opportunity to try and clean up what I wrote prior. I posted a small expample above.

To me, I made it almost "VB" like. Instead of complex select statements with nested logic, I made a variable for each item I needed, and some extra variables for logic. I set each of these variables with it's own select statement, if they passed the logic i had layed out (most of them were initialized with a value). Again, to me, I envisioned each variable being set like a property or a function in VB. I ended up having over 15 simple selects to set variables, and one select at the end to return (get) all of the variables.

The code ended up being much easier to read, and I cut the original code in half, but I greatly increased the amount of select statements. I did some time statistics to see which was faster, and the new query ended up being twice as fast, doing the same work.

My question: Is the way I described and used an accepted way practice? Did I make it some what clearer of what I did or am trying to do?

|||

I would post some data and examples, but I don't think it's relevant. I am not looking for how to do something, but the best way to do something.

For example: "Are you stupid? Don't do it like that." or "Yeah, that's fine."

|||How did you measure the statistics? Just by looking at the time in the query analyzer window? Check out some of the SET STATISTICS IO ON and SET STATISTICS TIME ON statements in books on line. then do the compare for before and after. If you are more comfortable with Profiler, get the aggregate reads on each of the tables. See which method requires less reads. From what you described it looks like you are reading the same tables more than once for each of those variables. I would get all the values you need into variables in as many few SELECTs as possible. Then work on the variables with all the formatting. Again, formatting is better done at the reporting layer. I think you use VB code in crystal reports right? So if you are more comfortable in VB you might want to move the formatting to the CR layer.

complex query help - count

Hi,
I am trying to add an aggregate function to the query below but I am not
able to get the intended results. I want to get the count to return total
downloads by each user but my query returns the total downloads by all users
.
Current Output
1,ttt,rrr,6/1/3005,30
2,ddd,jjj,5/31/2005,30
3,ppp,yyy,5/20/2005,30
Desired Output
1,ttt,rrr,6/1/3005,15
2,ddd,jjj,5/31/2005,5
3,ppp,yyy,5/20/2005,10
QUERY:
select distinct spl. [main_id],fname,lname,subscription_ends,
count(download_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
CREATE TABLE [dbo].[Main] (
[main_id] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
CREATE TABLE [dbo].[subscribers] (
[main_id] [numeric](18, 0) NOT NULL ,
[confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subscription_starts] [datetime] NOT NULL
)
GO
CREATE TABLE [dbo].[Downloads] (
[main_id] [numeric](18, 0) NOT NULL ,
[download_id] [numeric](18, 0) NOT NULL
)
GOMike,
Try this,
select distinct spl. [main_id],fname,lname,subscription_ends,
count(main_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
and downloads.main_id = m.main_id
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
Thanks
"Mike" wrote:

> Hi,
> I am trying to add an aggregate function to the query below but I am not
> able to get the intended results. I want to get the count to return total
> downloads by each user but my query returns the total downloads by all use
rs.
> Current Output
> 1,ttt,rrr,6/1/3005,30
> 2,ddd,jjj,5/31/2005,30
> 3,ppp,yyy,5/20/2005,30
>
> Desired Output
> 1,ttt,rrr,6/1/3005,15
> 2,ddd,jjj,5/31/2005,5
> 3,ppp,yyy,5/20/2005,10
> QUERY:
> select distinct spl. [main_id],fname,lname,subscription_ends,
> count(download_id)
> from
> main m,
> subscribers spl,
> downloads
> where
> m.main_id = spl.main_id
> and
> spl.confnum like 'T12%'
> and
> subscription_ends > = Getdate()
> group by
> spl.[main_id],
> fname,lname,subscription_ends
> order by
> subscription_ends DESC
> CREATE TABLE [dbo].[Main] (
> [main_id] [int] IDENTITY (1, 1) NOT NULL ,
> [fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> GO
>
> CREATE TABLE [dbo].[subscribers] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subscription_starts] [datetime] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[Downloads] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [download_id] [numeric](18, 0) NOT NULL
> )
> GO
>

Complex Query - Need help

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!Are you just looking for something like this?

select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate|||No, I need total for each month for each employee, here is a sample of what I am looking for:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ...... $341

Thanks a lot for your help!|||Is an employee's rate going to change? If so what rate should be displayed?|||It will use group by "corpcode, EmpName, rate". So if rate changes for an employee, there should be a new line in the query output.

Thanks again!|||select corpcode
, EmpName
, rate
, sum(Amount) as test
, Sum (CASE DATEPART(yy,reportdate)
WHEN 1 THEN Amount Else 0) As January
End,
, Sum (CASE DATEPART(yy,reportdate)
WHEN 2 THEN Amount Else 0) As February
End,
...List rest oh the months here
...

FROM <table>
Group By corpcode, EmpName, rate|||I am getting this error message:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

also instead of DATEPART(yy,reportdate), don't we need DATEPART(mm,reportdate)?

Thanks again!|||I believe jaraba's response will still have all the months in seperate rows. You may have to do something like this. I am not sure if there is a better way it is just the first thing that came to mind.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

select corpcode, EmpName, rate,
(SELECT top 1 Amount From #tmp a where mymonth = 1
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as January,
(SELECT top 1 Amount From #tmp a where mymonth = 2
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as February
...
from #tmp

drop table #tmp

This will only display the most recent months, if you want years too you could have a whole mess of columns.

Hope this helps|||If you don't mind, send me some data in an excel spreadsheet. i will work a solution for you.|||Thanks a lot, you guys are big help.

I got jaraba's query to work, but I want to ask one more question,

Whenever CorpCode changes, I need a line for sub totals and grand total as follows:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ......$341
----------------------
Sub-Total--2 employees--$134--$903--$690--$458.........$1019
----------------------
B1114444--ABC Zeb--$15--$13--$46--$0.00 ...... $68.0
B1114444--TTT Bob--$11--$0--$23--$48.0 .......$31
B1114444--GTH Bob--$19--$70--$3--$8.0 .........$15
----------------------
Sub-Total--3 employees--$45--$83--$73--$56..........$114
----------------------
----------------------
Grand Total--5 employees--$189--$986--$763--$514.........$1133
----------------------|||Actually, my response was no better. Sorry, I think something like this should get you what you are looking for.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

CREATE TABLE #emp (corpcode int, EmpName varchar(50), rate int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

insert into #emp
SELECT DISTINCT corpcode, EmpName, rate
FROM #tmp

select #emp.corpcode, #emp.EmpName, #emp.rate, j.Amount as January, f.Amount as February ...
from #emp
LEFT OUTER join #tmp j on #emp.corpcode = j.corpcode
and #emp.EmpName = j.EmpName
and #emp.rate = j.rate and j.mymonth = 1
LEFT OUTER join #tmp f on #emp.corpcode = f.corpcode
and #emp.EmpName = f.EmpName
and #emp.rate = f.rate and f.mymonth = 2
...

drop table #tmp
drop table #emp

Sorry for the confusion.|||Sorry again, you can ignore my last post if jaraba's query got you what you are looking for. If you want subtotals for each corpcode you will have to write a seperate query or use the functionality of your report writer.|||Look up COMPUTE BY in BOL