Prev: Install SQL 2008 express sp1
Next: Linked server query
From: Qaspec on 26 Jul 2010 17:19
I have the following query. I would like 'createnamerole' to become the
columns and 'ordersourcedesc' to become the rows with the count values being
the data contained between. I beleive I should use PIVOT but am unaware how.


Select b.OrderSourceDesc, b.CreateNameRole, Count(b.OrderID) as Countof

From

(Select a.OrderID, a.CustomerNumber, a.OrderDate, a.ProductID,
S.OrderSourceDesc, a.CreateNameRole

From dbo.tblOrderSource S With (nolock) Inner join

(SELECT O.OrderID, O.CustomerNumber, O.OrderDate, OL.ProductID,
O.OrderSourceID, O.CreateNameRole
FROM dbo.tblOrder O With (nolock) INNER JOIN
dbo.tblOrder_Line OL With (NoLock) ON O.OrderID =
OL.OrderID

Where O.OrderDate Between '6/1/10' and '7/1/10' and OL.ProductID
In('AGA04426','AGB5548'))a

On a.OrderSourceID = S.OrderSourceID)b

From: Erland Sommarskog on 27 Jul 2010 04:39
Qaspec (Qaspec(a)discussions.microsoft.com) writes:
> I have the following query. I would like 'createnamerole' to become the
> columns and 'ordersourcedesc' to become the rows with the count values
> being the data contained between. I beleive I should use PIVOT but am
> unaware how.

The general principle to build a PIVOT in this case would be

Select b.OrderSourceDesc,
Role1 = SUM(CASE WHEN b.CreateNameRole = 'Role1' THEN 1 END),
Role2 = SUM(CASE WHEN b.CreateNameRole = 'Role2' THEN 1 END),
...
From (Select a.OrderID, a.CustomerNumber, a.OrderDate, a.ProductID,
S.OrderSourceDesc, a.CreateNameRole
From dbo.tblOrderSource S
join (SELECT O.OrderID, O.CustomerNumber, O.OrderDate,
OL.ProductID, O.OrderSourceID, O.CreateNameRole
FROM dbo.tblOrder O
JOIN dbo.tblOrder_Line OL ON O.OrderID = OL.OrderID
Where O.OrderDate Between '6/1/10' and '7/1/10'
and OL.ProductID In('AGA04426','AGB5548')) a
On a.OrderSourceID = S.OrderSourceID)b
GROUP BY b.OrderSourceDesc

Now, if you don't know the role names in advance, there is no way
you can express this in a static query, since a SELECT statement
always returns a fixed result set with known columns. But you can
compose a query dynamically after first having queried for the
actual values. See http://www.sommarskog.se/dynamic_sql.html#Crosstab
for some ideas.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Install SQL 2008 express sp1
Next: Linked server query