Prev: Parse Name Field into First Name Last Name
Next: statistic data about procedures
From: Plamen Ratchev on 11 Dec 2009 20:37
There are a few problems with the query. First, the PIVOT operator groups by all columns from the source table(s) that
are not included in the PIVOT arguments (the aggregate columns and the column used to spread the pivoted values). This
is why it is a good practice to use a derived table and select only the columns needed. Next, you have your count column
as NCHAR and you cannot use the SUM aggregate. You can use the MAX/MIN aggregate functions or cast the value to numeric.
Casting has to be done in the derived table because the PIVOT operator does not allow functions as argument expressions.

Here is corrected version of the query that works with your tables:

SELECT name, [Clicked], [Opened], [Sent], [Views]
FROM (
SELECT P.name, CAST([count] AS INT) AS cnt, tt.TranDescription
FROM Transactions AS t
INNER JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID) AS T
PIVOT
(SUM(cnt) FOR TranDescription
IN ([Clicked], [Opened], [Sent], [Views])
) AS P;

Here is version that will work on SQL Server 2000:

SELECT P.name,
SUM(CASE WHEN tt.TranDescription = 'Clicked' THEN CAST([count] AS INT) ELSE 0 END) AS clicked_cnt,
SUM(CASE WHEN tt.TranDescription = 'Opened' THEN CAST([count] AS INT) ELSE 0 END) AS opened_cnt,
SUM(CASE WHEN tt.TranDescription = 'Sent' THEN CAST([count] AS INT) ELSE 0 END) AS sent_cnt,
SUM(CASE WHEN tt.TranDescription = 'Views' THEN CAST([count] AS INT) ELSE 0 END) AS views_cnt
FROM Transactions AS t
INNER JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID
GROUP BY name;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 12 Dec 2009 11:17
tshad (toms(a)pdsa.com) writes:
> I have been trying that. But I can't get it to work. This is working
> with a Pivot table. I wanted to get this to work first but remember I
> have to run this on an Sql 2000 server and I don't think you can use
> PIVOT on that, can you?

Right, the PIVOT keyword is not available on SQL 2000.

The structure for a pivot query is:

SELECT CustomerID,
MAX(CASE Product THEN 'Wigdet' THEN SalesAmt END) AS Widget,
MAX(CASE Product THEN 'Gadget' THEN SalesAmt END) AS Gadget,
...
FROM ...
GROUP BY CustomerID

The MAX here is only to get all one row. The MAX only sees one value,
and you could just as well use MIN.

PIVOT permits you to write this with a different syntax, but once you've
learnt to master above, you have little reason to learn PIVOT. (I never
use the PIVOT keyword myself).

Since you want this to be dynamic, you need to generate the above,
and this is more work on SQL 2000 than on SQL 2005. Or you can grab a
copy of RAC: http://www.rac4sql.net


--
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

From: tshad on 12 Dec 2009 19:21
Makes sense.

But how would you do it to make it dynamic.

In my case, I am going to be actually grouping by types of records. There
will be about 4 or 5 records in the one group with one set of columns and
another 4 or 5 with a different set of columns.

In our case the columns are going to be links that people that get the
emails will select select. One set of clients will have 5 links that their
users will click on in their emails and another set of clients will have a
different set of urls in their emails. These links (urls) will be the
column names and the values will be the number of clicks each client had
from the emails

So it would be something like:

E-Blase1 url1 url2 url3 url4
Client A 5 10 12
Client B 8 10
Client C 7 9 3 2
__________________________
Sub Total 12 27 15 12

E-Blase2 url5 url6 url7 url8
Client D 2 30 10
Client E 5 8 3 19
Client F 17 3 3 8
__________________________
Sub Total 22 13 36 37
__________________________
Total 34 40 51 49

Would I do the same thing I did with the PIVOT to create a dynamic SQL
statement?

Thanks,

Tom

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CDFAFF8B3E5Yazorman(a)127.0.0.1...
> tshad (toms(a)pdsa.com) writes:
>> I have been trying that. But I can't get it to work. This is working
>> with a Pivot table. I wanted to get this to work first but remember I
>> have to run this on an Sql 2000 server and I don't think you can use
>> PIVOT on that, can you?
>
> Right, the PIVOT keyword is not available on SQL 2000.
>
> The structure for a pivot query is:
>
> SELECT CustomerID,
> MAX(CASE Product THEN 'Wigdet' THEN SalesAmt END) AS Widget,
> MAX(CASE Product THEN 'Gadget' THEN SalesAmt END) AS Gadget,
> ...
> FROM ...
> GROUP BY CustomerID
>
> The MAX here is only to get all one row. The MAX only sees one value,
> and you could just as well use MIN.
>
> PIVOT permits you to write this with a different syntax, but once you've
> learnt to master above, you have little reason to learn PIVOT. (I never
> use the PIVOT keyword myself).
>
> Since you want this to be dynamic, you need to generate the above,
> and this is more work on SQL 2000 than on SQL 2005. Or you can grab a
> copy of RAC: http://www.rac4sql.net
>
>
> --
> 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
>


From: Erland Sommarskog on 13 Dec 2009 06:13
tshad (tfs(a)dslextreme.com) writes:
> But how would you do it to make it dynamic.

You would have to build dynamic SQL - or get RAC to do it for you.



--
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

From: tshad on 14 Dec 2009 18:57
This works fine but now I ran into a problem that doesn't lend itself to the
dynamic sql.

This has to be View. I got it all working and then realized I can't use
this in a view.

Is there a way to turn this into a view?
*********************************************
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + TranDescription + ']',
'[' + TranDescription+ ']')
FROM Transactions AS t
INNER JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID
group by TranDescription

Declare @query varchar(8000)
SET @query = '

SELECT name,Count,TranDescription
FROM Transactions AS t
INNER JOIN Personnel AS p
ON t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
ON t.tranTypeID = tt.TranTypeID
PIVOT
(
Count
For TranDescription
in (' + @columns + ')
) AS p'

Execute(@query)
************************************

The problem is that this is being executed by a reporting engine (of which I
have no control) and the it expects a View. It works fine as a stored
procedure but it needs to be a view and also as mentioned before needs to
run on SQL Server 2000.

Thanks,

Tom

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CE07C4EFC109Yazorman(a)127.0.0.1...
> tshad (tfs(a)dslextreme.com) writes:
>> But how would you do it to make it dynamic.
>
> You would have to build dynamic SQL - or get RAC to do it for you.
>
>
>
> --
> 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
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Parse Name Field into First Name Last Name
Next: statistic data about procedures