I've got a fairly tricky report to produce, so I'm trying to build it up bit
by bit, but I've fallen at the first hurdle!
I need to produce a view that lists all PartNo's and for each, what quantity
is in what location, e.g.
PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
WE12024, 5, null, 3, 10, 18
WE12067, 1, 1, null, 5, 7
etc...
I have the SQL to extract each bit of the data, but the problem is
presenting it in this way.
SQL:
Select PartNo, Count(*) as InGoodsIn
from Filters
Where LocationID = 15 /* for GoodsIn */
The PostInspection, Quarrantine & Stock locations have the following
LocationIDs - 16, 14, 17
How do I combine these result into one structure as indicated? Can somebody
point me in the right direction,please?
[In case it isn't clear from the above, the Filters table has this
structure: SerialNo (primary key), PartNo, LocationID]
Thanks in advance
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%237q365qlFHA.360@.TK2MSFTNGP09.phx.gbl...
> I've got a fairly tricky report to produce, so I'm trying to build it up
> bit by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what
> quantity is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can
> somebody point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
>
Apologies - problem solved.
I was on the right lines but had made a couple of errors. The solution I
have is:
SELECT Partno,
[GoodsIn] = SUM(CASE LocationID WHEN 15 THEN 1 ELSE 0 END),
[PostInspection] = SUM(CASE LocationID WHEN 16 THEN 1 ELSE 0 END),
[Quarantine] = SUM(CASE LocationID WHEN 14 THEN 1 ELSE 0 END),
[Stock] = SUM(CASE LocationID WHEN 17 THEN 1 ELSE 0 END)
FROM Filters
GROUP BY PartNo|||Try,
select
PartNo,
sum(case when location = 15 then 1 else 0 end) as GoodsIn,
sum(case when location = 16 then 1 else 0 end) as PostInspection,
sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
sum(case when location = 17 then 1 else 0 end) as Stock
from
Filters
order by
PartNo
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"CJM" wrote:
> I've got a fairly tricky report to produce, so I'm trying to build it up b
it
> by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what quanti
ty
> is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can somebod
y
> point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Try,
> select
> PartNo,
> sum(case when location = 15 then 1 else 0 end) as GoodsIn,
> sum(case when location = 16 then 1 else 0 end) as PostInspection,
> sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
> sum(case when location = 17 then 1 else 0 end) as Stock
> from
> Filters
> order by
> PartNo
> go
>
Curious...
Your syntax is different to mine but still works - eg. "Case when LocationID
= 15" rather than "Case LocationID when 15"
Is this difference significant?
Chris|||The result of both expressions are the same. See CASE in BOL to read more
about "simple case" and "search case" formats.
AMB
"CJM" wrote:
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Curious...
> Your syntax is different to mine but still works - eg. "Case when Location
ID
> = 15" rather than "Case LocationID when 15"
> Is this difference significant?
> Chris
>
>
Showing posts with label produce. Show all posts
Showing posts with label produce. Show all posts
Saturday, February 25, 2012
Friday, February 24, 2012
Cross-Tab Query
I need to produce a query in cross-tab format that displays the column data as the first day of the week for every week for a given date range.
i.e. for August
4 Aug 03 | 11 Aug 03 | 18 Aug 03 | 25 Aug 03
Row1
Row2
etc...
Any pointer in the right direction would be appreciated.
Thanks
TimThe basic idea is to make a query, which returns the value to be accumulated with the corresponding week indication, like:
SELECT YourGroupingField,
case datediff(wk, '2003-01-01', getdate()) < 30 THEN YourSumField ELSE 0 END AS Aug0,
case datediff(wk, '2003-01-01', getdate()) = 30 THEN YourSumField ELSE 0 END AS Aug1,
case datediff(wk, '2003-01-01', getdate()) = 31 THEN YourSumField ELSE 0 END AS Aug2,
case datediff(wk, '2003-01-01', getdate()) > 31 THEN YourSumField ELSE 0 END AS Aug3
FROM YourTable
You may use this query as a subquery or as a database view:
SELECT YourGroupingField, sum(Aug0), sum(Aug1), ...
FROM (YourQuery)
GROUP BY YourGroupingField
Let me know if this helps, or when you don't understand my outline.|||Thank you for your quick reply.
I think I did not explain myself that well. So here goes - the column dates should auto-generate based on a date range. This date range could potentially be several months or years. So if the date range was 1 March 03 - 30 June 03 this would produde 18 columns starting with 3 Mar 03.
I know how to write a normal cross-tab but it's the auto-column generation that's tricky for me.|||You want the heavy stuff? Look at this (www.sqlmag.com/Articles/Index.cfm?ArticleID=15608).
i.e. for August
4 Aug 03 | 11 Aug 03 | 18 Aug 03 | 25 Aug 03
Row1
Row2
etc...
Any pointer in the right direction would be appreciated.
Thanks
TimThe basic idea is to make a query, which returns the value to be accumulated with the corresponding week indication, like:
SELECT YourGroupingField,
case datediff(wk, '2003-01-01', getdate()) < 30 THEN YourSumField ELSE 0 END AS Aug0,
case datediff(wk, '2003-01-01', getdate()) = 30 THEN YourSumField ELSE 0 END AS Aug1,
case datediff(wk, '2003-01-01', getdate()) = 31 THEN YourSumField ELSE 0 END AS Aug2,
case datediff(wk, '2003-01-01', getdate()) > 31 THEN YourSumField ELSE 0 END AS Aug3
FROM YourTable
You may use this query as a subquery or as a database view:
SELECT YourGroupingField, sum(Aug0), sum(Aug1), ...
FROM (YourQuery)
GROUP BY YourGroupingField
Let me know if this helps, or when you don't understand my outline.|||Thank you for your quick reply.
I think I did not explain myself that well. So here goes - the column dates should auto-generate based on a date range. This date range could potentially be several months or years. So if the date range was 1 March 03 - 30 June 03 this would produde 18 columns starting with 3 Mar 03.
I know how to write a normal cross-tab but it's the auto-column generation that's tricky for me.|||You want the heavy stuff? Look at this (www.sqlmag.com/Articles/Index.cfm?ArticleID=15608).
Crosstab in TSQL
Does anyone know of any code to produce a crosstab table or
temp table? I found several examples of stored procs to do this,
however they just produce results. I need to produce a table
to use to update another table.
Thanks much,
Marc MillerA simple one
USE pubs
GO
SELECT
stor_id,
SUM(CASE YEAR(ord_date)
WHEN 1992 THEN qty
ELSE 0
END) AS c1992,
SUM(CASE YEAR(ord_date)
WHEN 1993 THEN qty
ELSE 0
END) AS c1993,
SUM(CASE YEAR(ord_date)
WHEN 1994 THEN qty
ELSE 0
END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||This article describes various techniques for implementing a crosstab type
query. Basically, it's just a group by query.
http://www.aspfaq.com/show.asp?id=2462
The result of any query can be output to a table or temporary table:
the SELECT.. INTO.. syntax will create a new table with the same column and
data type structure as the query result.
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
into
mycrosstab
from
mytable
group by
a,
b
the INSERT INTO.. SELECT.. syntax will insert the query result into an
existing table:
insert into mycrosstab
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
from
mytable
group by
a,
b
For temporary tables, just prefix the insert table with a # symbol.
insert into #mycrosstab . . .
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||Uri,
Thanks! I took your suggestion a step further, becuase I the field names
can be variable. Here's what I did
(just for posterity I suppose, and anyone else trying to do the same.)
Temp table ##c_bt:
CUST_ACCT COLUMN_NAME AMOUNT
0123456 January 500.00
5464646 March 53.00
5161616 January 333.33
0123456 May 500.00
5464646 June 53.00
5161616 June 333.33
etc.
CODE:
DECLARE @.col varchar(20)
DECLARE @.DDL varchar(1000)
DECLARE curcols CURSOR
FOR select distinct column_name from ##c_bt order by column_name
OPEN curcols
SET @.DDL = 'Select cust_acct, '
FETCH NEXT FROM curcols INTO @.col
WHILE (@.@.fetch_status <> -1)
BEGIN
set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
amount ELSE 0 END) AS ' + rtrim(@.col) + ','
FETCH NEXT FROM curcols INTO @.col
END
CLOSE curcols
DEALLOCATE curcols
SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
EXEC(@.DDL)
Thanks again,
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>A simple one
> USE pubs
> GO
> SELECT
> stor_id,
> SUM(CASE YEAR(ord_date)
> WHEN 1992 THEN qty
> ELSE 0
> END) AS c1992,
> SUM(CASE YEAR(ord_date)
> WHEN 1993 THEN qty
> ELSE 0
> END) AS c1993,
> SUM(CASE YEAR(ord_date)
> WHEN 1994 THEN qty
> ELSE 0
> END) AS c1994
> FROM Sales
> GROUP BY stor_id
> ORDER BY stor_id
>
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
>|||Marc
Two questions I'd liek to ask you.
1) Why do you need to use a global temporary table?
2) Why do you need to use a cursor?
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
> Uri,
> Thanks! I took your suggestion a step further, becuase I the field names
> can be variable. Here's what I did
> (just for posterity I suppose, and anyone else trying to do the same.)
> Temp table ##c_bt:
> CUST_ACCT COLUMN_NAME AMOUNT
> 0123456 January 500.00
> 5464646 March 53.00
> 5161616 January 333.33
> 0123456 May 500.00
> 5464646 June
> 53.00
> 5161616 June 333.33
> etc.
> CODE:
> DECLARE @.col varchar(20)
> DECLARE @.DDL varchar(1000)
> DECLARE curcols CURSOR
> FOR select distinct column_name from ##c_bt order by column_name
> OPEN curcols
> SET @.DDL = 'Select cust_acct, '
> FETCH NEXT FROM curcols INTO @.col
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
> amount ELSE 0 END) AS ' + rtrim(@.col) + ','
> FETCH NEXT FROM curcols INTO @.col
> END
> CLOSE curcols
> DEALLOCATE curcols
> SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
> SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
> EXEC(@.DDL)
> Thanks again,
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>|||Uri,
1) The temp table need not be global, it was just the way I was
experimenting, but I do need a table
to join and update another table.
2) I'm using the cursor to loop through its results to discover the 'column
names' that I need to build
my SQL. The column names can differ from month to month for me,
depending on the source data.
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
> Marc
> Two questions I'd liek to ask you.
> 1) Why do you need to use a global temporary table?
> 2) Why do you need to use a cursor?
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||Marc
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
I don't see any reasons to use a global temrorary table. Pls refer to the
BOL for more details
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
>
Search on internet "dynamic crosstab" written by Itzik Ben-Gan
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uvNydJ0JGHA.2864@.TK2MSFTNGP10.phx.gbl...
> Uri,
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
>|||Check out the RAC utility for pivoting/xtabs,
no coding necessary
www.rac4sql.net|||Is there an easy way, in outlook express, to stop this guy's spam from
showing up based on their alias?
I am sure that others use noname@.noname.com just to avoid being put on
mailing lists, and I don't want to filter out useful messages inadvertantly.
Thanks,
Jim
"05ponyGT" <noname@.noname.com> wrote in message
news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
> Check out the RAC utility for pivoting/xtabs,
> no coding necessary
> www.rac4sql.net
>|||Is that better:)
Have a nice day.
www.rac4sql.net
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:epx%23Ir1JGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Is there an easy way, in outlook express, to stop this guy's spam from
> showing up based on their alias?
> I am sure that others use noname@.noname.com just to avoid being put on
> mailing lists, and I don't want to filter out useful messages
> inadvertantly.
> Thanks,
> Jim
> "05ponyGT" <noname@.noname.com> wrote in message
> news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
>
temp table? I found several examples of stored procs to do this,
however they just produce results. I need to produce a table
to use to update another table.
Thanks much,
Marc MillerA simple one
USE pubs
GO
SELECT
stor_id,
SUM(CASE YEAR(ord_date)
WHEN 1992 THEN qty
ELSE 0
END) AS c1992,
SUM(CASE YEAR(ord_date)
WHEN 1993 THEN qty
ELSE 0
END) AS c1993,
SUM(CASE YEAR(ord_date)
WHEN 1994 THEN qty
ELSE 0
END) AS c1994
FROM Sales
GROUP BY stor_id
ORDER BY stor_id
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||This article describes various techniques for implementing a crosstab type
query. Basically, it's just a group by query.
http://www.aspfaq.com/show.asp?id=2462
The result of any query can be output to a table or temporary table:
the SELECT.. INTO.. syntax will create a new table with the same column and
data type structure as the query result.
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
into
mycrosstab
from
mytable
group by
a,
b
the INSERT INTO.. SELECT.. syntax will insert the query result into an
existing table:
insert into mycrosstab
select
a,
b,
sum(cnt) as totcnt,
avg(cnt) as avgcnt
from
mytable
group by
a,
b
For temporary tables, just prefix the insert table with a # symbol.
insert into #mycrosstab . . .
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
> Does anyone know of any code to produce a crosstab table or
> temp table? I found several examples of stored procs to do this,
> however they just produce results. I need to produce a table
> to use to update another table.
> Thanks much,
> Marc Miller
>|||Uri,
Thanks! I took your suggestion a step further, becuase I the field names
can be variable. Here's what I did
(just for posterity I suppose, and anyone else trying to do the same.)
Temp table ##c_bt:
CUST_ACCT COLUMN_NAME AMOUNT
0123456 January 500.00
5464646 March 53.00
5161616 January 333.33
0123456 May 500.00
5464646 June 53.00
5161616 June 333.33
etc.
CODE:
DECLARE @.col varchar(20)
DECLARE @.DDL varchar(1000)
DECLARE curcols CURSOR
FOR select distinct column_name from ##c_bt order by column_name
OPEN curcols
SET @.DDL = 'Select cust_acct, '
FETCH NEXT FROM curcols INTO @.col
WHILE (@.@.fetch_status <> -1)
BEGIN
set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
amount ELSE 0 END) AS ' + rtrim(@.col) + ','
FETCH NEXT FROM curcols INTO @.col
END
CLOSE curcols
DEALLOCATE curcols
SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
EXEC(@.DDL)
Thanks again,
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>A simple one
> USE pubs
> GO
> SELECT
> stor_id,
> SUM(CASE YEAR(ord_date)
> WHEN 1992 THEN qty
> ELSE 0
> END) AS c1992,
> SUM(CASE YEAR(ord_date)
> WHEN 1993 THEN qty
> ELSE 0
> END) AS c1993,
> SUM(CASE YEAR(ord_date)
> WHEN 1994 THEN qty
> ELSE 0
> END) AS c1994
> FROM Sales
> GROUP BY stor_id
> ORDER BY stor_id
>
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:ObOs2SzJGHA.1544@.TK2MSFTNGP11.phx.gbl...
>|||Marc
Two questions I'd liek to ask you.
1) Why do you need to use a global temporary table?
2) Why do you need to use a cursor?
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
> Uri,
> Thanks! I took your suggestion a step further, becuase I the field names
> can be variable. Here's what I did
> (just for posterity I suppose, and anyone else trying to do the same.)
> Temp table ##c_bt:
> CUST_ACCT COLUMN_NAME AMOUNT
> 0123456 January 500.00
> 5464646 March 53.00
> 5161616 January 333.33
> 0123456 May 500.00
> 5464646 June
> 53.00
> 5161616 June 333.33
> etc.
> CODE:
> DECLARE @.col varchar(20)
> DECLARE @.DDL varchar(1000)
> DECLARE curcols CURSOR
> FOR select distinct column_name from ##c_bt order by column_name
> OPEN curcols
> SET @.DDL = 'Select cust_acct, '
> FETCH NEXT FROM curcols INTO @.col
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> set @.DDL = @.DDL + ' SUM(CASE column_name WHEN ''' + rtrim(@.col) +''' THEN
> amount ELSE 0 END) AS ' + rtrim(@.col) + ','
> FETCH NEXT FROM curcols INTO @.col
> END
> CLOSE curcols
> DEALLOCATE curcols
> SET @.DDL = SUBSTRING(@.DDL,0,LEN(@.DDL)-1)
> SET @.DDL = @.DDL + ' FROM ##c_bt GROUP BY cust_acct ORDER BY cust_acct'
> EXEC(@.DDL)
> Thanks again,
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23MG2$WzJGHA.1728@.TK2MSFTNGP14.phx.gbl...
>|||Uri,
1) The temp table need not be global, it was just the way I was
experimenting, but I do need a table
to join and update another table.
2) I'm using the cursor to loop through its results to discover the 'column
names' that I need to build
my SQL. The column names can differ from month to month for me,
depending on the source data.
Marc
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
> Marc
> Two questions I'd liek to ask you.
> 1) Why do you need to use a global temporary table?
> 2) Why do you need to use a cursor?
>
> "Marc Miller" <mm1284@.hotmail.com> wrote in message
> news:uLCuj6zJGHA.720@.TK2MSFTNGP14.phx.gbl...
>|||Marc
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
I don't see any reasons to use a global temrorary table. Pls refer to the
BOL for more details
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
>
Search on internet "dynamic crosstab" written by Itzik Ben-Gan
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:uvNydJ0JGHA.2864@.TK2MSFTNGP10.phx.gbl...
> Uri,
> 1) The temp table need not be global, it was just the way I was
> experimenting, but I do need a table
> to join and update another table.
> 2) I'm using the cursor to loop through its results to discover the
> 'column names' that I need to build
> my SQL. The column names can differ from month to month for me,
> depending on the source data.
> Marc
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7c5bC0JGHA.1032@.TK2MSFTNGP10.phx.gbl...
>|||Check out the RAC utility for pivoting/xtabs,
no coding necessary
www.rac4sql.net|||Is there an easy way, in outlook express, to stop this guy's spam from
showing up based on their alias?
I am sure that others use noname@.noname.com just to avoid being put on
mailing lists, and I don't want to filter out useful messages inadvertantly.
Thanks,
Jim
"05ponyGT" <noname@.noname.com> wrote in message
news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
> Check out the RAC utility for pivoting/xtabs,
> no coding necessary
> www.rac4sql.net
>|||Is that better:)
Have a nice day.
www.rac4sql.net
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:epx%23Ir1JGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Is there an easy way, in outlook express, to stop this guy's spam from
> showing up based on their alias?
> I am sure that others use noname@.noname.com just to avoid being put on
> mailing lists, and I don't want to filter out useful messages
> inadvertantly.
> Thanks,
> Jim
> "05ponyGT" <noname@.noname.com> wrote in message
> news:uN0pYl1JGHA.596@.TK2MSFTNGP10.phx.gbl...
>
Subscribe to:
Posts (Atom)