September 25, 2003 at 6:38 pm
Hi,
I've got a classical situtation where the records from my query needs to be pivoted
eg.
record1
record2
record3
record4
...
to become
record1 record2 record3 record4....
I have my query sorted out, and it responds in 2 minutes (quite reasonable). I have a reporting tool that will do the pivoting for me, but since there are so many records being passed to it, and the report has to pivot that data, the time taken is quite extreme.
If I write out the query to select each column, I would then have to hardcode my values into the query, but the results would be a lot faster. This also means the query is not dynamic ie. add a new record type, and the query must be modified as a result.
Has anybody got any tricks or suggestions in regards to pulling this data straight from SQL, without introducing another translating layer?
thanks
September 25, 2003 at 10:37 pm
One way I can suggest is getting all the unique values needed for buliding the columns
in a temporary table.
Then using dynamic SQl you could build your Case statements.
I believe I had done something similar.
Would post the query after I get my hands on it.
He who knows others is learned but the wise one is one who knows himself.
He who knows others is learned but the wise one is one who knows himself.
September 25, 2003 at 11:47 pm
Try this Out
==============
declare @sqlstring varchar(1000)
declare @currentchar varchar(10)
declare @mysqlstring varchar(8000)
set @sqlstring ='sum( case customersales.cutomerid when ''XXX'' then customersales.totalsales else 0 end )'
set @mysqlstring =''
set @currentchar =''
declare @m varchar(8000)
declare @start int
declare @value int
set @start=0
set @value =1
set @m=''
select @m=@m +','+ cast(customerid as varchar(10))from customers order by customerid
set @m=substring(@m,2,len(@m)) + ','
while @value <>0
begin
Set @value =charindex(',',@m,@start)
If @value >0
Begin
set @currentchar=substring(@m,@start,@value-@start)
set @mysqlstring=@mysqlstring + ',' + replace(@sqlstring,'XXX',@currentchar) + 'As ' + ''''+ @currentchar + ''''
End
SET @start=@value+1
end
set @mysqlstring='select ' + substring( @mysqlstring,2,len( @mysqlstring))
set @mysqlstring=@mysqlstring + ' from customers inner join customersales on customers.customerid=customersales.cutomerid '
Exec (@mysqlstring)
Tables
======
CREATE TABLE [customers] (
[CustomerId] [smallint] NOT NULL ,
[CustomerName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[outstanding] [money] NULL ,
CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED
(
[CustomerId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
create table customersales
(cutomerid int, totalsales money)
He who knows others is learned but the wise one is one who knows himself.
He who knows others is learned but the wise one is one who knows himself.
September 28, 2003 at 6:12 pm
cheers,
I actually came to the same conclusion myself - create a dynamic SQL string, and then execute to get the format I want. My next problem after that was the limitation on the strings (8000 char), and my SQL query worked out much bigger than that (my output was 140+ columns).
However, I found that although you can't create a string larger than 8000 char, you can EXECUTE these strings together as one.
ie. String1 as varchar(8000)
String2 as varchar(8000)
EXEC (String1 + String2)
Got there in the end, Thanks for the help,
cheers
September 29, 2003 at 6:13 am
Just to have a clear picture of your query. Do you want a transpose of your result set? Thatis converting your rows to columns and vice versa. In that case you would need to look into the cross tab query concepts.
Thanks
Lucas
October 6, 2003 at 1:54 am
Hi Brendon,
quote:
I've got a classical situtation where the records from my query needs to be pivotedeg.
record1
record2
record3
record4
...
to become
record1 record2 record3 record4....
I have my query sorted out, and it responds in 2 minutes (quite reasonable). I have a reporting tool that will do the pivoting for me, but since there are so many records being passed to it, and the report has to pivot that data, the time taken is quite extreme.
I think that these kinds of manipulation is not the job of SQL Server, but some front end report writer.
quote:
Has anybody got any tricks or suggestions in regards to pulling this data straight from SQL, without introducing another translating layer?
BOL has an example on cross tabs or pivot or use this one
http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply