|
|
You're in the
sql server programming
group:Turning data into columns
sql server programming:
Debbie Croft (debbie.croft@nospamforme.com) writes:
[quoted text, click to view] > I have a database that has a table similar to the following: > > ID Company Year Value > ---------------------- > 1 CompanyA 1998 378 > 2 CompanyB 1998 45 > 3 CompanyC 1998 245 > 4 CompanyA 1999 754 > 5 CompanyB 1999 298 > 6 CompanyC 1999 7 > 7 CompanyA 2000 347 > 8 CompanyB 2000 32 > 9 CompanyC 2000 186 > > I'd like to output this as: > > Company 1998 1999 2000 > ----------------------- > CompanyA 378 754 347 > CompanyB 45 298 32 > CompanyC 245 7 186 > > The problem is that I'd don't know before hand what years are in the > database - they change as new data is added. > > What's the best way to write a query that performs this transformation? > Can it be done using set logic (i.e. without procedural code)? A SELECT query produces a table. A table has column with known names, and therefore also the number of column must be known. Thus, if there can be any number of years in the table, you cannot write this in a single SELECT. If you can assume that there are at most, say, three years, you can write it as a query, but you need to accept that the year cannot be indicated in the column header, but that will have to be a separate column. Here is a query for SQL 2005 that produces this: CREATE TABLE somevalues (Company varchar(12) NOT NULL, year char(4) NOT NULL, value int NOT NULL, PRIMARY KEY (Company, year)) go INSERT somevalues (Company, year, value) SELECT 'CompanyA', '1998', 378 UNION ALL SELECT 'CompanyB', '1998', 45 UNION ALL SELECT 'CompanyC', '1998', 245 UNION ALL SELECT 'CompanyA', '1999', 754 UNION ALL SELECT 'CompanyB', '1999', 298 UNION ALL SELECT 'CompanyC', '1999', 7 UNION ALL SELECT 'CompanyA', '2000', 347 UNION ALL SELECT 'CompanyB', '2000', 32 UNION ALL SELECT 'CompanyC', '2000', 186 go SELECT Company, Year1 = MIN(CASE y.rn WHEN 1 THEN s.year END), Value_year1 = MIN(CASE y.rn WHEN 1 THEN s.value END), Year2 = MIN(CASE y.rn WHEN 2 THEN s.year END), Value_year3 = MIN(CASE y.rn WHEN 2 THEN s.value END), Year3 = MIN(CASE y.rn WHEN 3 THEN s.year END), Value_year2 = MIN(CASE y.rn WHEN 3 THEN s.value END) FROM somevalues s JOIN (SELECT DISTINCT year, rn = dense_rank() OVER (ORDER BY year) FROM somevalues) AS y ON s.year = y.year GROUP BY Company go DROP TABLE somevalues If you absolute must have the column names dynamic and/or have a dynamic set of columns, you must build the query dynamically and execute it with sp_executesql. This is quite steep increase in complexity. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
I have a database that has a table similar to the following:
ID Company Year Value ---------------------- 1 CompanyA 1998 378 2 CompanyB 1998 45 3 CompanyC 1998 245 4 CompanyA 1999 754 5 CompanyB 1999 298 6 CompanyC 1999 7 7 CompanyA 2000 347 8 CompanyB 2000 32 9 CompanyC 2000 186 I'd like to output this as: Company 1998 1999 2000 ----------------------- CompanyA 378 754 347 CompanyB 45 298 32 CompanyC 245 7 186 The problem is that I'd don't know before hand what years are in the database - they change as new data is added. What's the best way to write a query that performs this transformation? Can it be done using set logic (i.e. without procedural code)?
If you need a good option for doing dynamic crosstab, Geckoware's crosstab
builder does a great job of dynamically building crosstab queries. http://www.geckoware.com.au/ This tool is much more efficient than temp tables / table variables as its an in-proc dll (xp_) which works in memory rather than on disk (as temp objects do). Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Benchmark your query performance http://www.SQLBenchmarkPro.com [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns995FF0D84A6D0Yazorman@127.0.0.1... > Debbie Croft (debbie.croft@nospamforme.com) writes: >> I have a database that has a table similar to the following: >> >> ID Company Year Value >> ---------------------- >> 1 CompanyA 1998 378 >> 2 CompanyB 1998 45 >> 3 CompanyC 1998 245 >> 4 CompanyA 1999 754 >> 5 CompanyB 1999 298 >> 6 CompanyC 1999 7 >> 7 CompanyA 2000 347 >> 8 CompanyB 2000 32 >> 9 CompanyC 2000 186 >> >> I'd like to output this as: >> >> Company 1998 1999 2000 >> ----------------------- >> CompanyA 378 754 347 >> CompanyB 45 298 32 >> CompanyC 245 7 186 >> >> The problem is that I'd don't know before hand what years are in the >> database - they change as new data is added. >> >> What's the best way to write a query that performs this transformation? >> Can it be done using set logic (i.e. without procedural code)? > > A SELECT query produces a table. A table has column with known names, and > therefore also the number of column must be known. Thus, if there can be > any number of years in the table, you cannot write this in a single > SELECT. > If you can assume that there are at most, say, three years, you can write > it as a query, but you need to accept that the year cannot be indicated > in the column header, but that will have to be a separate column. > > Here is a query for SQL 2005 that produces this: > > CREATE TABLE somevalues (Company varchar(12) NOT NULL, > year char(4) NOT NULL, > value int NOT NULL, > PRIMARY KEY (Company, year)) > go > INSERT somevalues (Company, year, value) > SELECT 'CompanyA', '1998', 378 UNION ALL > SELECT 'CompanyB', '1998', 45 UNION ALL > SELECT 'CompanyC', '1998', 245 UNION ALL > SELECT 'CompanyA', '1999', 754 UNION ALL > SELECT 'CompanyB', '1999', 298 UNION ALL > SELECT 'CompanyC', '1999', 7 UNION ALL > SELECT 'CompanyA', '2000', 347 UNION ALL > SELECT 'CompanyB', '2000', 32 UNION ALL > SELECT 'CompanyC', '2000', 186 > go > SELECT Company, > Year1 = MIN(CASE y.rn WHEN 1 THEN s.year END), > Value_year1 = MIN(CASE y.rn WHEN 1 THEN s.value END), > Year2 = MIN(CASE y.rn WHEN 2 THEN s.year END), > Value_year3 = MIN(CASE y.rn WHEN 2 THEN s.value END), > Year3 = MIN(CASE y.rn WHEN 3 THEN s.year END), > Value_year2 = MIN(CASE y.rn WHEN 3 THEN s.value END) > FROM somevalues s > JOIN (SELECT DISTINCT year, > rn = dense_rank() OVER (ORDER BY year) > FROM somevalues) AS y ON s.year = y.year > GROUP BY Company > go > DROP TABLE somevalues > > > If you absolute must have the column names dynamic and/or have a > dynamic set of columns, you must build the query dynamically and > execute it with sp_executesql. This is quite steep increase in > complexity. > > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Greg Linwood (g_linwood@hotmail.com) writes:
[quoted text, click to view] > If you need a good option for doing dynamic crosstab, Geckoware's crosstab > builder does a great job of dynamically building crosstab queries. > http://www.geckoware.com.au/ > > This tool is much more efficient than temp tables / table variables as its > an in-proc dll (xp_) which works in memory rather than on disk (as temp > objects do). XPs? Isn't time to make a CLR solution for SQL2005? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Not necessarily - arrays are slower in CLR than in xp_'s so doing this would
mean a performance trade off. Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Benchmark your query performance http://www.SQLBenchmarkPro.com [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns99606B704204EYazorman@127.0.0.1... > Greg Linwood (g_linwood@hotmail.com) writes: >> If you need a good option for doing dynamic crosstab, Geckoware's >> crosstab >> builder does a great job of dynamically building crosstab queries. >> http://www.geckoware.com.au/ >> >> This tool is much more efficient than temp tables / table variables as >> its >> an in-proc dll (xp_) which works in memory rather than on disk (as temp >> objects do). > > XPs? Isn't time to make a CLR solution for SQL2005? > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Greg Linwood (g_linwood@hotmail.com) writes:
[quoted text, click to view] > Not necessarily - arrays are slower in CLR than in xp_'s so doing this > would mean a performance trade off. On the other hand, there is a context switch to invoke an XP which is costly. Then again, I can't really see that this matters. To run a dynamic pivot, you need to: 1) Get the distinct values of the column you want to pivot. 2) Somehow traverse that data to build a SELECT statement. 3) Run that SELECT statement. Reasonably, it most cases the cost will be for the first and last steps, and whether you make the second step in T-SQL, C# or C++ may not give any measurable difference in performance. However, particularly C# is a more friendly environment to build the SQL string in. Or does this XP read all data with a regular query, and then restructure the result set, before it is returned to the client. In this case, there may be some advantage with native code or managed code. But would it not be even better to do this reshaping client-side? An XP or a CLR procedure that allocates large amounts of memory for its own purposes is not very friendly to SQL Server. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Context switches are only costly if they happen at high reptition.
Running the second step in T-SQL usually means hammering the disk with IO which puts performance problems into a higher dimension. There is a big difference in array management between C# & C++. Regards, Greg Linwood SQL Server MVP http://blogs.sqlserver.org.au/blogs/greg_linwood Benchmark your query performance http://www.SQLBenchmarkPro.com [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns99608C677236DYazorman@127.0.0.1... > Greg Linwood (g_linwood@hotmail.com) writes: >> Not necessarily - arrays are slower in CLR than in xp_'s so doing this >> would mean a performance trade off. > > On the other hand, there is a context switch to invoke an XP which is > costly. > > Then again, I can't really see that this matters. To run a dynamic pivot, > you need to: > 1) Get the distinct values of the column you want to pivot. > 2) Somehow traverse that data to build a SELECT statement. > 3) Run that SELECT statement. > > Reasonably, it most cases the cost will be for the first and last steps, > and whether you make the second step in T-SQL, C# or C++ may not give > any measurable difference in performance. However, particularly C# is a > more friendly environment to build the SQL string in. > > Or does this XP read all data with a regular query, and then restructure > the result set, before it is returned to the client. In this case, there > may be some advantage with native code or managed code. But would it not > be even better to do this reshaping client-side? An XP or a CLR procedure > that allocates large amounts of memory for its own purposes is not very > friendly to SQL Server. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Performance is king. But what few seem to be aware of is what is
left in its wake. And what could possibly be good about something slow? www.beyondsql.blogspot.com [quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message news:elWYv74uHHA.3816@TK2MSFTNGP05.phx.gbl... > If you need a good option for doing dynamic crosstab, Geckoware's crosstab > builder does a great job of dynamically building crosstab queries. > http://www.geckoware.com.au/ > > This tool is much more efficient than temp tables / table variables as its > an in-proc dll (xp_) which works in memory rather than on disk (as temp > objects do). > > Regards, > Greg Linwood > SQL Server MVP > http://blogs.sqlserver.org.au/blogs/greg_linwood > Benchmark your query performance > http://www.SQLBenchmarkPro.com > > "Erland Sommarskog" <esquel@sommarskog.se> wrote in message > news:Xns995FF0D84A6D0Yazorman@127.0.0.1... >> Debbie Croft (debbie.croft@nospamforme.com) writes: >>> I have a database that has a table similar to the following: >>> >>> ID Company Year Value >>> ---------------------- >>> 1 CompanyA 1998 378 >>> 2 CompanyB 1998 45 >>> 3 CompanyC 1998 245 >>> 4 CompanyA 1999 754 >>> 5 CompanyB 1999 298 >>> 6 CompanyC 1999 7 >>> 7 CompanyA 2000 347 >>> 8 CompanyB 2000 32 >>> 9 CompanyC 2000 186 >>> >>> I'd like to output this as: >>> >>> Company 1998 1999 2000 >>> ----------------------- >>> CompanyA 378 754 347 >>> CompanyB 45 298 32 >>> CompanyC 245 7 186 >>> >>> The problem is that I'd don't know before hand what years are in the >>> database - they change as new data is added. >>> >>> What's the best way to write a query that performs this transformation? >>> Can it be done using set logic (i.e. without procedural code)? >> >> A SELECT query produces a table. A table has column with known names, and >> therefore also the number of column must be known. Thus, if there can be >> any number of years in the table, you cannot write this in a single >> SELECT. >> If you can assume that there are at most, say, three years, you can write >> it as a query, but you need to accept that the year cannot be indicated >> in the column header, but that will have to be a separate column. >> >> Here is a query for SQL 2005 that produces this: >> >> CREATE TABLE somevalues (Company varchar(12) NOT NULL, >> year char(4) NOT NULL, >> value int NOT NULL, >> PRIMARY KEY (Company, year)) >> go >> INSERT somevalues (Company, year, value) >> SELECT 'CompanyA', '1998', 378 UNION ALL >> SELECT 'CompanyB', '1998', 45 UNION ALL >> SELECT 'CompanyC', '1998', 245 UNION ALL >> SELECT 'CompanyA', '1999', 754 UNION ALL >> SELECT 'CompanyB', '1999', 298 UNION ALL >> SELECT 'CompanyC', '1999', 7 UNION ALL >> SELECT 'CompanyA', '2000', 347 UNION ALL >> SELECT 'CompanyB', '2000', 32 UNION ALL >> SELECT 'CompanyC', '2000', 186 >> go >> SELECT Company, >> Year1 = MIN(CASE y.rn WHEN 1 THEN s.year END), >> Value_year1 = MIN(CASE y.rn WHEN 1 THEN s.value END), >> Year2 = MIN(CASE y.rn WHEN 2 THEN s.year END), >> Value_year3 = MIN(CASE y.rn WHEN 2 THEN s.value END), >> Year3 = MIN(CASE y.rn WHEN 3 THEN s.year END), >> Value_year2 = MIN(CASE y.rn WHEN 3 THEN s.value END) >> FROM somevalues s >> JOIN (SELECT DISTINCT year, >> rn = dense_rank() OVER (ORDER BY year) >> FROM somevalues) AS y ON s.year = y.year >> GROUP BY Company >> go >> DROP TABLE somevalues >> >> >> If you absolute must have the column names dynamic and/or have a >> dynamic set of columns, you must build the query dynamically and >> execute it with sp_executesql. This is quite steep increase in >> complexity. >> >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >
Greg Linwood (g_linwood@hotmail.com) writes:
[quoted text, click to view] > Running the second step in T-SQL usually means hammering the disk with IO > which puts performance problems into a higher dimension. Hammering the disk with I/O? Come on! You have a temp table with, say, 30 rows in it. It will be written to disk when the data is inserted, and then read from cache. [quoted text, click to view] > There is a big difference in array management between C# & C++. Maybe. But it is not going be significant to handle 30 values. Or for that matter 4096 which is the most you will be able to cram into the query anyway. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |