ansaurus

Question

Help with t-sql query

Answer 1

+2 A:

Use Pivot tables..

A simple example..

    USE AdventureWorks;
GO

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture; 



DaysToManufacture  AverageCost  
0                  5.0885  
1                  223.88  
2                  359.1082  
4                  949.4105

Query

    SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,   
[0], [1], [2], [3], [4]  
FROM  
(SELECT DaysToManufacture, StandardCost   
    FROM Production.Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) 
) AS PivotTable;

Result

Cost_Sorted_By_Production_Days   0                     1                     2                     3                     4

AverageCost                    5.0885                223.88                359.1082              NULL                  949.4105

Broken Link 2010-06-18 17:27:32

Pivot tables are the answer. Get the data formatted how you want first by using DatePart ( http://msdn.microsoft.com/en-us/library/ms174420.aspx ) then you can start playing around with the pivot function

Greg Bray 2010-06-18 17:35:31

Answer 2

+3 A:

What you are seeking is commonly called a crosstab query. If what you are asking is how to build a crosstab query given a static list of columns, you can do something like so:

Select Title
    , Min( Case When DatePart(mm, [Date]) = 7 And DatePart(yy, [Date]) = 2010 Then MetaData End ) As [Jul-10]
    , Min( Case When DatePart(mm, [Date]) = 8 And DatePart(yy, [Date]) = 2010 Then MetaData End ) As [Aug-10]   
    , Min( Case When DatePart(mm, [Date]) = 9 And DatePart(yy, [Date]) = 2010 Then MetaData End ) As [Sep-10]       
    ...
From Table
Where [Date] Between @StartDate And @EndDate
Group By Title

Similarly, you can use the PIVOT functionality as suggested by Broken Link. However, both the above solution and the PIVOT functionality rely on static column declarations. If what you want is a dynamic list of columns (a.k.a. dynamic crosstab), then you are outside the bounds of what T-SQL was primarily designed to do. It is possible with some fugly dynamic SQL but it is brittle and cumbersome. Instead, you should build the resultset in a middle-tier component or use a reporting tool that will build crosstab results.

Thomas 2010-06-18 17:43:21

personally i prefer the case inside aggregate method over the PIVOT keyword so +rep

Jeremy 2010-06-18 17:50:02

@Thomas: I wanted a dynamic list but i will stick to a static list for now. That leads me to another question but i guess i will ask a seperate one. Thanks for the answer.

stackoverflowuser 2010-06-18 20:36:04

I use sql coalesce http://msdn.microsoft.com/en-us/library/ms190349.aspx for building the dynamic list.. and you can use this with pivot but not with crosstab query.!

Broken Link 2010-06-18 21:12:11

@Broken Link - Coalesce simply returns the first non-null value and all values must be implicitly convertible to the same type. E.g., `Coalesce('2001-01-01',42,'DOG')` will not work. I'm not sure I understand what you mean by "not with crosstab query".

Thomas 2010-06-18 22:27:09

Let's say the user wants to pass the list dynamic, If you see my answer the query has hard coded values as 'DaysToManufacture IN ([0], [1], [2], [3], [4])' If you want the list to be dynamically passed you can use coalesce like this select @list = COALESCE( @list + ',[' + product + ']' ,'[' + product + ']') from productlistand pass the list to that pivot query.. Ref: http://stackoverflow.com/questions/2283674/pivot-sql-values-from-subquery

Broken Link 2010-06-18 23:31:54

@Broken Link - There are two solutions in the link you provided. In one, the developer simply created placeholder columns (1,2,3,4,5,6) and dynamically determined what data goes in each column. Ok. "Something" will still need to determine what the columns represent and that will likely be client code. The second solution is dynamic SQL. My recommendation is to avoid dynamic T-SQL like the plague and instead do it in the middle-tier component. T-SQL is awful for string manipulation.

Thomas 2010-06-19 01:17:29

ansaurus

tags:

views:

answers:

Help with t-sql query

related questions