Display results horizontal and not vertical

I have set of tables that are displaying transactions one row per 
transaction.  I have 3 types of transactions I want to display but I want to 
display them on one line - one line per person.

Each transaction has 2 number - a count and a value.

I am trying to get it to display:

Person     Sent Count    Sent Value     Opened Count     Opened Value 
Clicks Count    Clicks Value

But my Select statement is:

SELECT Name,Count,Value
FROM Transactions t
JOIN Personnel p on t.PersonnelID = p.PersonnelID
JOIN TranType tt on t.tranTypeID = tt.TranTypeID
Where TranDescription = 'Sent' OR TranDescription = 'Opened' OR 
TranDescription = 'Clicked'

And displays:

Name               Count      Value
------------------ ---------- ------------
Joe Smith           10        15.20
Joe Smith           15        200.00
Joe Smith           22        10.00
Larry Jones         23        23.10
Larry Jones         200       1501.00
Larry Jones         300       120.00
Frank Garret        33        310.00

How do I make a select statement that will give me only 3 only 3 rows (one 
for each Name) and put all the counts and values on the same line?

My tables and inserts:

CREATE TABLE [dbo].[Personnel](
 [PersonnelID] [int] NOT NULL,
 [Name] [varchar](50) NULL,
 CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
 (
  [PersonnelID] ASC
 )
)

CREATE TABLE [dbo].[TranType](
 [TranTypeID] [int] NOT NULL,
 [TranDescription] [varchar](50) NULL,
 CONSTRAINT [PK_TranType] PRIMARY KEY CLUSTERED
 (
  [TranTypeID] ASC
 )
)

CREATE TABLE [dbo].[Transactions](
 [TransactionID] [int] NOT NULL,
 [tranTypeID] [int] NULL,
 [PersonnelID] [int] NULL,
 [Count] [nchar](10) NULL,
 [Value] [decimal](10, 2) NULL,
 CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
 (
  [TransactionID] ASC
 )
)

INSERT Personnel(PersonnelID,Name)
VALUES (1,'Joe Smith')
INSERT Personnel(PersonnelID,Name)
VALUES (2,'Larry Jones')
INSERT Personnel(PersonnelID,Name)
VALUES (3,'Frank Garret')


INSERT TranType(TranTypeID,TranDescription)
VALUES (7,'Sent')
INSERT TranType(TranTypeID,TranDescription)
VALUES (21,'Opened')
INSERT TranType(TranTypeID,TranDescription)
VALUES (35,'Clicked')
INSERT TranType(TranTypeID,TranDescription)
VALUES (42,'Views')

INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (1,7,1,10,15.20)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (2,21,1,15,200.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (3,35,1,22,10.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (4,42,1,50,25.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (5,7,2,23,23.10)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (6,21,2,200,1501.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (7,35,2,300,120.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (8,42,2,250,15.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (9,7,3,33,310.00)
INSERT Transactions(TransactionID,tranTypeID,PersonnelID,Count,Value)
VALUES (10,42,3,200,50.10)

And yes Joe, I know there are many nulls other no-nos.  These tables are 
just to show what I am trying to accomplish.

Thanks,

Tom 


1
tshad
12/9/2009 1:21:11 AM
sqlserver.programming 1873 articles. 0 followers. Follow

24 Replies
976 Views

Similar Articles

[PageSpeed] 20

Here is how you can pivot the data:

SELECT name,
        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS sent_count,
        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS sent_value,
        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS opened_count,
        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS opened_value,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS clicks_count,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS clicks_value
FROM Transactions AS t
JOIN Personnel AS p
   ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
   ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

-- 
Plamen Ratchev
http://www.SQLStudio.com
1
Plamen
12/9/2009 2:31:09 AM
I also tried doing the CASE before but didn't group them so they were all on 
different lines.

This works great.

Thanks,

Tom
"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:sM-dnZHDHsR0loLWnZ2dnUVZ_vZi4p2d@speakeasy.net...
> Here is how you can pivot the data:
>
> SELECT name,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
> sent_count,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
> sent_value,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
> AS opened_count,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
> opened_value,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
> AS clicks_count,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
> clicks_value
> FROM Transactions AS t
> JOIN Personnel AS p
>   ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
>   ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


1
tshad
12/9/2009 5:20:36 PM
Is there a way to sum different columns such as the set_count, opened_count 
and clicks_count?  I tried to sum the sums (which you can't do) but it shows 
what I am trying to do?

Would I need to do a SubQuery to get this to work?

What I am trying to do is something like:


SELECT name,
        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
sent_count,
        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
sent_value,
        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS 
opened_count,
        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
opened_value,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
AS clicks_count,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
clicks_value,
        SUM(SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) 
+
              SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 
END) +
              SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 
END)) as totalCounts
FROM Transactions AS t
JOIN Personnel AS p
   ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
   ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

I know this doesn't work, but I am trying to add values that would normally 
be in different rows if there was no Group by clause.

Thanks,

Tom

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:sM-dnZHDHsR0loLWnZ2dnUVZ_vZi4p2d@speakeasy.net...
> Here is how you can pivot the data:
>
> SELECT name,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
> sent_count,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
> sent_value,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
> AS opened_count,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
> opened_value,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
> AS clicks_count,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
> clicks_value
> FROM Transactions AS t
> JOIN Personnel AS p
>   ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
>   ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


0
tshad
12/9/2009 9:00:16 PM
I could do something like:

SELECT name, sent_count, sent_value, opened_count, opened_value, 
clicks_count, clicks_value,
 sent_count + opened_count + clicks_count as total_counts
FROM (
SELECT name,
        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
sent_count,
        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
sent_value,
        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS 
opened_count,
        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
opened_value,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
AS clicks_count,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
clicks_value
FROM Transactions AS t
JOIN Personnel AS p
   ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
   ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name) as A

and it works but was wonding if there is a better (different) way to do this 
without the SubQuery?

Thanks,

Tom

"tshad" <toms@pdsa.com> wrote in message 
news:uNeNdKReKHA.2164@TK2MSFTNGP02.phx.gbl...
> Is there a way to sum different columns such as the set_count, 
> opened_count and clicks_count?  I tried to sum the sums (which you can't 
> do) but it shows what I am trying to do?
>
> Would I need to do a SubQuery to get this to work?
>
> What I am trying to do is something like:
>
>
> SELECT name,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
> sent_count,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
> sent_value,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
> AS opened_count,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
> opened_value,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
> AS clicks_count,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
> clicks_value,
>        SUM(SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) 
> +
>              SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 
> END) +
>              SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 
> END)) as totalCounts
> FROM Transactions AS t
> JOIN Personnel AS p
>   ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
>   ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> I know this doesn't work, but I am trying to add values that would 
> normally be in different rows if there was no Group by clause.
>
> Thanks,
>
> Tom
>
> "Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
> news:sM-dnZHDHsR0loLWnZ2dnUVZ_vZi4p2d@speakeasy.net...
>> Here is how you can pivot the data:
>>
>> SELECT name,
>>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
>> sent_count,
>>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
>> sent_value,
>>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
>> AS opened_count,
>>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
>> opened_value,
>>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
>> AS clicks_count,
>>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) 
>> AS clicks_value
>> FROM Transactions AS t
>> JOIN Personnel AS p
>>   ON t.PersonnelID = p.PersonnelID
>> JOIN TranType AS tt
>>   ON t.tranTypeID = tt.TranTypeID
>> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
>> GROUP BY name;
>>
>> -- 
>> Plamen Ratchev
>> http://www.SQLStudio.com
>
> 


0
tshad
12/9/2009 9:07:46 PM
On Wed, 9 Dec 2009 13:07:46 -0800, tshad wrote:

>I could do something like:
>
>SELECT name, sent_count, sent_value, opened_count, opened_value, 
>clicks_count, clicks_value,
> sent_count + opened_count + clicks_count as total_counts
>FROM (
>SELECT name,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
>sent_count,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
>sent_value,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS 
>opened_count,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
>opened_value,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
>AS clicks_count,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
>clicks_value
>FROM Transactions AS t
>JOIN Personnel AS p
>   ON t.PersonnelID = p.PersonnelID
>JOIN TranType AS tt
>   ON t.tranTypeID = tt.TranTypeID
>WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
>GROUP BY name) as A
>
>and it works but was wonding if there is a better (different) way to do this 
>without the SubQuery?

Hi Tom,

SELECT     name,
           SUM(CASE WHEN TranDescription = 'Sent'
                    THEN [count] ELSE 0 END) AS sent_count,
           SUM(CASE WHEN TranDescription = 'Sent'
                    THEN value ELSE 0 END) AS sent_value,
           SUM(CASE WHEN TranDescription = 'Opened'
                    THEN [count] ELSE 0 END) AS opened_count,
           SUM(CASE WHEN TranDescription = 'Opened'
                    THEN value ELSE 0 END) AS opened_value,
           SUM(CASE WHEN TranDescription = 'Clicked'
                    THEN [count] ELSE 0 END) AS clicks_count,
           SUM(CASE WHEN TranDescription = 'Clicked'
                    THEN value ELSE 0 END) AS clicks_value,
           SUM([count]) AS total_counts
FROM       Transactions AS t
INNER JOIN Personnel AS p
      ON   t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
      ON   t.tranTypeID = tt.TranTypeID
WHERE      TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY   name;

And I advice you to add the correct prefix to the name, TranDescription,
count, and value columns. It improves human understanding and proofs
against possible problems is the table design ever changes.

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
0
Hugo
12/9/2009 10:44:29 PM
Using a derived table like you did is a good way to handle it to avoid repeating the expressions.

Here is how you can do the same repeating the expressions:

SELECT name,
        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS sent_count,
        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS sent_value,
        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS opened_count,
        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS opened_value,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS clicks_count,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS clicks_value,
        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) +
        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) +
        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS total_count
FROM Transactions AS t
JOIN Personnel AS p
   ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
   ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

Since you already filter on the transaction descriptions, then it can be simplified to this:

SELECT name,
        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS sent_count,
        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS sent_value,
        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) AS opened_count,
        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS opened_value,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) AS clicks_count,
        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS clicks_value,
        SUM(CAST([count] AS INT)) AS total_count
FROM Transactions AS t
JOIN Personnel AS p
   ON t.PersonnelID = p.PersonnelID
JOIN TranType AS tt
   ON t.tranTypeID = tt.TranTypeID
WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
GROUP BY name;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
12/9/2009 10:46:26 PM
So all three do the job.

I assume yours is a little more efficient as you don't have to create a 
derived table.

Thanks,

Tom


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:a9ednS4ZrfEjtb3WnZ2dnUVZ_jpi4p2d@speakeasy.net...
> Using a derived table like you did is a good way to handle it to avoid 
> repeating the expressions.
>
> Here is how you can do the same repeating the expressions:
>
> SELECT name,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
> sent_count,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
> sent_value,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
> AS opened_count,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
> opened_value,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
> AS clicks_count,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
> clicks_value,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) +
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) +
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
> AS total_count
> FROM Transactions AS t
> JOIN Personnel AS p
>   ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
>   ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> Since you already filter on the transaction descriptions, then it can be 
> simplified to this:
>
> SELECT name,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS 
> sent_count,
>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS 
> sent_value,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
> AS opened_count,
>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS 
> opened_value,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END) 
> AS clicks_count,
>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) AS 
> clicks_value,
>        SUM(CAST([count] AS INT)) AS total_count
> FROM Transactions AS t
> JOIN Personnel AS p
>   ON t.PersonnelID = p.PersonnelID
> JOIN TranType AS tt
>   ON t.tranTypeID = tt.TranTypeID
> WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY name;
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 


0
tshad
12/9/2009 11:41:25 PM
Derived tables and CTEs in general have no effect on performance, they are not materialized and the optimizer generates 
a single execution plan the query.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
12/10/2009 12:00:01 AM
How would I do a similar thing where I don't know what the TranDescriptions 
were but I want them to be the columns (except for the name).

If I do:

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

I get the following:

Name                Count       TranDescription
Joe Smith          10             Sent
Joe Smith          15            Opened
Joe Smith          22            Clicked
Joe Smith          50            Views
Larry Jones       23            Sent
Larry Jones      200           Opened
Larry Jones      300           Clicked
Larry Jones      250           Views
Frank Garret    33             Sent
Frank Garret    200           Views

But I want the each TranDescriptions to be a column, like:

Name             Sent     Opened    Clicked    Views
Joe Smith       10        15             22            50
Larry Jones    23        200           300          250
Frank Garret  33                                         200

If nobody has Opened, I don't want there to be a column for Opened.

I assume I could use a Pivot table here but I have to run this on an Sql 
2000 server.

Thanks,

Tom

"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message 
news:pr90i59n4jtsseq2j13g4kuekev493niqa@4ax.com...
> On Wed, 9 Dec 2009 13:07:46 -0800, tshad wrote:
>
>>I could do something like:
>>
>>SELECT name, sent_count, sent_value, opened_count, opened_value,
>>clicks_count, clicks_value,
>> sent_count + opened_count + clicks_count as total_counts
>>FROM (
>>SELECT name,
>>        SUM(CASE WHEN TranDescription = 'Sent' THEN [count] ELSE 0 END) AS
>>sent_count,
>>        SUM(CASE WHEN TranDescription = 'Sent' THEN value ELSE 0 END) AS
>>sent_value,
>>        SUM(CASE WHEN TranDescription = 'Opened' THEN [count] ELSE 0 END) 
>> AS
>>opened_count,
>>        SUM(CASE WHEN TranDescription = 'Opened' THEN value ELSE 0 END) AS
>>opened_value,
>>        SUM(CASE WHEN TranDescription = 'Clicked' THEN [count] ELSE 0 END)
>>AS clicks_count,
>>        SUM(CASE WHEN TranDescription = 'Clicked' THEN value ELSE 0 END) 
>> AS
>>clicks_value
>>FROM Transactions AS t
>>JOIN Personnel AS p
>>   ON t.PersonnelID = p.PersonnelID
>>JOIN TranType AS tt
>>   ON t.tranTypeID = tt.TranTypeID
>>WHERE TranDescription IN ('Sent', 'Opened', 'Clicked')
>>GROUP BY name) as A
>>
>>and it works but was wonding if there is a better (different) way to do 
>>this
>>without the SubQuery?
>
> Hi Tom,
>
> SELECT     name,
>           SUM(CASE WHEN TranDescription = 'Sent'
>                    THEN [count] ELSE 0 END) AS sent_count,
>           SUM(CASE WHEN TranDescription = 'Sent'
>                    THEN value ELSE 0 END) AS sent_value,
>           SUM(CASE WHEN TranDescription = 'Opened'
>                    THEN [count] ELSE 0 END) AS opened_count,
>           SUM(CASE WHEN TranDescription = 'Opened'
>                    THEN value ELSE 0 END) AS opened_value,
>           SUM(CASE WHEN TranDescription = 'Clicked'
>                    THEN [count] ELSE 0 END) AS clicks_count,
>           SUM(CASE WHEN TranDescription = 'Clicked'
>                    THEN value ELSE 0 END) AS clicks_value,
>           SUM([count]) AS total_counts
> FROM       Transactions AS t
> INNER JOIN Personnel AS p
>      ON   t.PersonnelID = p.PersonnelID
> INNER JOIN TranType AS tt
>      ON   t.tranTypeID = tt.TranTypeID
> WHERE      TranDescription IN ('Sent', 'Opened', 'Clicked')
> GROUP BY   name;
>
> And I advice you to add the correct prefix to the name, TranDescription,
> count, and value columns. It improves human understanding and proofs
> against possible problems is the table design ever changes.
>
> -- 
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis 


0
tshad
12/11/2009 11:50:29 PM
On Fri, 11 Dec 2009 15:50:29 -0800, tshad wrote:

>How would I do a similar thing where I don't know what the TranDescriptions 
>were but I want them to be the columns (except for the name).
>
>If I do:
>
>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
>
>I get the following:
>
>Name                Count       TranDescription
>Joe Smith          10             Sent
>Joe Smith          15            Opened
>Joe Smith          22            Clicked
>Joe Smith          50            Views
>Larry Jones       23            Sent
>Larry Jones      200           Opened
>Larry Jones      300           Clicked
>Larry Jones      250           Views
>Frank Garret    33             Sent
>Frank Garret    200           Views
>
>But I want the each TranDescriptions to be a column, like:
>
>Name             Sent     Opened    Clicked    Views
>Joe Smith       10        15             22            50
>Larry Jones    23        200           300          250
>Frank Garret  33                                         200
>
>If nobody has Opened, I don't want there to be a column for Opened.
>
>I assume I could use a Pivot table here but I have to run this on an Sql 
>2000 server.

Hi Tom,

Google "dynamic pivot sql server".

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
0
Hugo
12/12/2009 12:13:05 AM
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?

But using the dynamic PIVOT style, I came up with:

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

print @query

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

This wouldn't work.  It gave me the error:

Msg 325, Level 15, State 1, Line 9
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level 
of the current database to a higher value to enable this feature. See help 
for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

I then tried to do:
ALTER DATABASE DigitalResultsDev SET COMPATIBILITY_LEVEL = 100

(not sure why I have to do that).

The script it came up with that didn't work was:

******************************************
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
(
 Sum([Count])
 For TranDescription
 in ([Clicked],[Opened],[Sent],[Views])
) AS p
******************************************

But this one gives me red squiggles under Name, Count and Transaction and 
says they are invalid column names.  If I run this by itself on another 
machine which is also an Sql 2008 machine, I get:

Msg 8117, Level 16, State 1, Line 1
Operand data type nchar is invalid for sum operator.
Msg 8156, Level 16, State 1, Line 12
The column 'PersonnelID' was specified multiple times for 'p'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Count'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TranDescription'.

but if I just highlight the Select statement down to the PIVOT, it works 
fine.

I then realized that Count was an nchar (just like the error said) and 
changed it to:
**********************************************
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 ([Clicked],[Opened],[Sent],[Views])
) AS p
***********************************************

But now I get:

Incorrect syntax near the keyword 'For' and 'For' and '[Clicked]' have red 
squiggles under them.

Why doesn't this work?  I am sure the format is wrong somehow but it looks 
right.

Thanks,

Tom


"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message 
news:mun5i5paivs9ri6p4oht6952gonbb4p6hv@4ax.com...
> On Fri, 11 Dec 2009 15:50:29 -0800, tshad wrote:
>
>>How would I do a similar thing where I don't know what the 
>>TranDescriptions
>>were but I want them to be the columns (except for the name).
>>
>>If I do:
>>
>>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
>>
>>I get the following:
>>
>>Name                Count       TranDescription
>>Joe Smith          10             Sent
>>Joe Smith          15            Opened
>>Joe Smith          22            Clicked
>>Joe Smith          50            Views
>>Larry Jones       23            Sent
>>Larry Jones      200           Opened
>>Larry Jones      300           Clicked
>>Larry Jones      250           Views
>>Frank Garret    33             Sent
>>Frank Garret    200           Views
>>
>>But I want the each TranDescriptions to be a column, like:
>>
>>Name             Sent     Opened    Clicked    Views
>>Joe Smith       10        15             22            50
>>Larry Jones    23        200           300          250
>>Frank Garret  33                                         200
>>
>>If nobody has Opened, I don't want there to be a column for Opened.
>>
>>I assume I could use a Pivot table here but I have to run this on an Sql
>>2000 server.
>
> Hi Tom,
>
> Google "dynamic pivot sql server".
>
> -- 
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis 


0
tshad
12/12/2009 12:42:37 AM
I tried to change Count to an int and change the sql back to:

*********************************************
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
(
 Sum([Count])
 For TranDescription
 in ([Clicked],[Opened],[Sent],[Views])
) AS p
*********************************************

and got:

Msg 8156, Level 16, State 1, Line 12
The column 'PersonnelID' was specified multiple times for 'p'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Count'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'TranDescription'.

and PersonnelID is not part of the result set and the Sql without the PIVOT 
works fine.

Tom

"tshad" <toms@pdsa.com> wrote in message 
news:uRNtBQseKHA.2780@TK2MSFTNGP05.phx.gbl...
>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?
>
> But using the dynamic PIVOT style, I came up with:
>
> *******************************************
> 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'
>
> print @query
>
> Execute(@query)
> *******************************************
>
> This wouldn't work.  It gave me the error:
>
> Msg 325, Level 15, State 1, Line 9
> Incorrect syntax near 'PIVOT'. You may need to set the compatibility level 
> of the current database to a higher value to enable this feature. See help 
> for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
>
> I then tried to do:
> ALTER DATABASE DigitalResultsDev SET COMPATIBILITY_LEVEL = 100
>
> (not sure why I have to do that).
>
> The script it came up with that didn't work was:
>
> ******************************************
> 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
> (
> Sum([Count])
> For TranDescription
> in ([Clicked],[Opened],[Sent],[Views])
> ) AS p
> ******************************************
>
> But this one gives me red squiggles under Name, Count and Transaction and 
> says they are invalid column names.  If I run this by itself on another 
> machine which is also an Sql 2008 machine, I get:
>
> Msg 8117, Level 16, State 1, Line 1
> Operand data type nchar is invalid for sum operator.
> Msg 8156, Level 16, State 1, Line 12
> The column 'PersonnelID' was specified multiple times for 'p'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'Count'.
> Msg 207, Level 16, State 1, Line 1
> Invalid column name 'TranDescription'.
>
> but if I just highlight the Select statement down to the PIVOT, it works 
> fine.
>
> I then realized that Count was an nchar (just like the error said) and 
> changed it to:
> **********************************************
> 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 ([Clicked],[Opened],[Sent],[Views])
> ) AS p
> ***********************************************
>
> But now I get:
>
> Incorrect syntax near the keyword 'For' and 'For' and '[Clicked]' have red 
> squiggles under them.
>
> Why doesn't this work?  I am sure the format is wrong somehow but it looks 
> right.
>
> Thanks,
>
> Tom
>
>
> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message 
> news:mun5i5paivs9ri6p4oht6952gonbb4p6hv@4ax.com...
>> On Fri, 11 Dec 2009 15:50:29 -0800, tshad wrote:
>>
>>>How would I do a similar thing where I don't know what the 
>>>TranDescriptions
>>>were but I want them to be the columns (except for the name).
>>>
>>>If I do:
>>>
>>>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
>>>
>>>I get the following:
>>>
>>>Name                Count       TranDescription
>>>Joe Smith          10             Sent
>>>Joe Smith          15            Opened
>>>Joe Smith          22            Clicked
>>>Joe Smith          50            Views
>>>Larry Jones       23            Sent
>>>Larry Jones      200           Opened
>>>Larry Jones      300           Clicked
>>>Larry Jones      250           Views
>>>Frank Garret    33             Sent
>>>Frank Garret    200           Views
>>>
>>>But I want the each TranDescriptions to be a column, like:
>>>
>>>Name             Sent     Opened    Clicked    Views
>>>Joe Smith       10        15             22            50
>>>Larry Jones    23        200           300          250
>>>Frank Garret  33                                         200
>>>
>>>If nobody has Opened, I don't want there to be a column for Opened.
>>>
>>>I assume I could use a Pivot table here but I have to run this on an Sql
>>>2000 server.
>>
>> Hi Tom,
>>
>> Google "dynamic pivot sql server".
>>
>> -- 
>> Hugo Kornelis, SQL Server MVP
>> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
> 


0
tshad
12/12/2009 12:54:07 AM
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
0
Plamen
12/12/2009 1:37:43 AM
tshad (toms@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@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

0
Erland
12/12/2009 4:17:09 PM
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@sommarskog.se> wrote in message 
news:Xns9CDFAFF8B3E5Yazorman@127.0.0.1...
> tshad (toms@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@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
> 


0
tshad
12/13/2009 12:21:59 AM
tshad (tfs@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@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

0
Erland
12/13/2009 11:13:11 AM
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@sommarskog.se> wrote in message 
news:Xns9CE07C4EFC109Yazorman@127.0.0.1...
> tshad (tfs@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@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
> 


0
tshad
12/14/2009 11:57:52 PM
Actually what I was doing was doing something like what Plamen mentioned 
using the dynamic sql mentioned below to create the sum statements.

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;

The problem is that I can't use EXEC or DECLARE in a View.   I then thought 
about doing the whole thing and calling it from a function, but I can't 
execute a Stored Procedure from a function.

I was thinking of using a Multi Statement Function that would use a couple 
of SELECTs to solve the problem

I am now looking at taking this statement and instead of putting the 
variable names in a variable as I do below, do something like:

drop table #Temp

Select tranDescription into #temp
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;

select * from #Temp

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;

Somehow replacing the CASE WHEN tt.TranDescription = 'Clicked'  with 
something that refers to the #Temp table.

Not sure if this can be done.

Tom

Now I have table with my column names.  But I can't think of how to use a 
SELECT with a Join on this temptable to
"tshad" <toms@pdsa.com> wrote in message 
news:egf0AlRfKHA.4636@TK2MSFTNGP04.phx.gbl...
> 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@sommarskog.se> wrote in message 
> news:Xns9CE07C4EFC109Yazorman@127.0.0.1...
>> tshad (tfs@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@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
>>
>
> 


0
tshad
12/15/2009 1:08:12 AM
As I understand it, you want to return a result set, and you want at run 
time to be able to vary the number and/or names of the columns that are 
returned.  You cannot do that with a view or a function.  The reason is that 
the query optimizer must be able to determine the names, number, and 
properties of the columns returned by the view or function before the view 
or function is called.  So the names, number, and properties of the columns 
to be returned are fixed when you do the CREATE VIEW or CREATE FUNCTION.

One possible workaround is to create a single view that returns all the 
possible desired sums.  Then your reporting software just gets the columns 
needed for the current report.  Depending on exactly what you are doing you 
may be able to do this without incurring a severe perofrmance penalty.  Most 
of the cost of a query is in the retrieval of the rows and sorting and 
grouping them.  The cost of doing sums and CASE expressions is often 
comparitively very small.  So, for example, you may find that the following 
two views give you essentially the same performance:

SELECT P.name,
        SUM(CASE WHEN tt.TranDescription = 'Clicked' THEN CAST([count] AS 
INT) ELSE 0 END) AS clicked_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;

and

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;

Tom

"tshad" <toms@pdsa.com> wrote in message 
news:etPTUMSfKHA.2780@TK2MSFTNGP05.phx.gbl...
> Actually what I was doing was doing something like what Plamen mentioned 
> using the dynamic sql mentioned below to create the sum statements.
>
> 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;
>
> The problem is that I can't use EXEC or DECLARE in a View.   I then 
> thought about doing the whole thing and calling it from a function, but I 
> can't execute a Stored Procedure from a function.
>
> I was thinking of using a Multi Statement Function that would use a couple 
> of SELECTs to solve the problem
>
> I am now looking at taking this statement and instead of putting the 
> variable names in a variable as I do below, do something like:
>
> drop table #Temp
>
> Select tranDescription into #temp
> 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;
>
> select * from #Temp
>
> 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;
>
> Somehow replacing the CASE WHEN tt.TranDescription = 'Clicked'  with 
> something that refers to the #Temp table.
>
> Not sure if this can be done.
>
> Tom
>
> Now I have table with my column names.  But I can't think of how to use a 
> SELECT with a Join on this temptable to
> "tshad" <toms@pdsa.com> wrote in message 
> news:egf0AlRfKHA.4636@TK2MSFTNGP04.phx.gbl...
>> 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@sommarskog.se> wrote in message 
>> news:Xns9CE07C4EFC109Yazorman@127.0.0.1...
>>> tshad (tfs@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@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
>>>
>>
>>
>
> 

0
Tom
12/15/2009 1:51:22 AM
This was why I was trying to use multiple ways of handling the system where 
I use a VIEW to call a Function and/or a Function to call a Stored 
procedure.

The problem is I run into a gotcha, such as you can't call a Stored 
procedure in a function.

My problem is that I don't know what the possible sums are.  This was what 
we did before with the SUMs.  The problem is I don't know what the names 
would be which is why I was trying to use a temp table and use the temp 
table in some way in a function.  Then use that with actual Select statement 
but can't find a way to create the column names from the temptables.

It may be that there isn't a way.  My problem is that I have this working 
fine as a Stored Procedure but can't call it from a Function or a View and I 
have to end up with a View that is called by the Report Engine.

Thanks,

Tom

"Tom Cooper" <tomcooper@comcast.net> wrote in message 
news:u46TfkSfKHA.1592@TK2MSFTNGP06.phx.gbl...
> As I understand it, you want to return a result set, and you want at run 
> time to be able to vary the number and/or names of the columns that are 
> returned.  You cannot do that with a view or a function.  The reason is 
> that the query optimizer must be able to determine the names, number, and 
> properties of the columns returned by the view or function before the view 
> or function is called.  So the names, number, and properties of the 
> columns to be returned are fixed when you do the CREATE VIEW or CREATE 
> FUNCTION.
>
> One possible workaround is to create a single view that returns all the 
> possible desired sums.  Then your reporting software just gets the columns 
> needed for the current report.  Depending on exactly what you are doing 
> you may be able to do this without incurring a severe perofrmance penalty. 
> Most of the cost of a query is in the retrieval of the rows and sorting 
> and grouping them.  The cost of doing sums and CASE expressions is often 
> comparitively very small.  So, for example, you may find that the 
> following two views give you essentially the same performance:
>
> SELECT P.name,
>        SUM(CASE WHEN tt.TranDescription = 'Clicked' THEN CAST([count] AS 
> INT) ELSE 0 END) AS clicked_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;
>
> and
>
> 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;
>
> Tom
>
> "tshad" <toms@pdsa.com> wrote in message 
> news:etPTUMSfKHA.2780@TK2MSFTNGP05.phx.gbl...
>> Actually what I was doing was doing something like what Plamen mentioned 
>> using the dynamic sql mentioned below to create the sum statements.
>>
>> 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;
>>
>> The problem is that I can't use EXEC or DECLARE in a View.   I then 
>> thought about doing the whole thing and calling it from a function, but I 
>> can't execute a Stored Procedure from a function.
>>
>> I was thinking of using a Multi Statement Function that would use a 
>> couple of SELECTs to solve the problem
>>
>> I am now looking at taking this statement and instead of putting the 
>> variable names in a variable as I do below, do something like:
>>
>> drop table #Temp
>>
>> Select tranDescription into #temp
>> 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;
>>
>> select * from #Temp
>>
>> 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;
>>
>> Somehow replacing the CASE WHEN tt.TranDescription = 'Clicked'  with 
>> something that refers to the #Temp table.
>>
>> Not sure if this can be done.
>>
>> Tom
>>
>> Now I have table with my column names.  But I can't think of how to use a 
>> SELECT with a Join on this temptable to
>> "tshad" <toms@pdsa.com> wrote in message 
>> news:egf0AlRfKHA.4636@TK2MSFTNGP04.phx.gbl...
>>> 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@sommarskog.se> wrote in message 
>>> news:Xns9CE07C4EFC109Yazorman@127.0.0.1...
>>>> tshad (tfs@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@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
>>>>
>>>
>>>
>>
>>
> 


0
tshad
12/15/2009 2:08:57 AM
I tried this and the result table is correct, but there are no column 
headings.  The headings are in then #temp table but I can't use them as an 
AS in my Select statement.
**********************************************************************
drop table #Temp

Create Table #Temp
(row int identity(1,1),
 TranDescription varchar(50))

Insert #temp(tranDescription)
Select 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;

select * from #Temp

SELECT P.name,tt.TranDescription,
 SUM(CASE WHEN Row = 1 and tt.TranDescription = tempt.TranDescription THEN 
Count ELSE 0 END),
 SUM(CASE WHEN Row = 2 and tt.TranDescription = tempt.TranDescription THEN 
Count ELSE 0 END),
 SUM(CASE WHEN Row = 3 and tt.TranDescription = tempt.TranDescription THEN 
Count ELSE 0 END),
 SUM(CASE WHEN Row = 4 and tt.TranDescription = tempt.TranDescription THEN 
Count ELSE 0 END)
FROM Transactions AS t
INNER JOIN Personnel AS p
    ON t.PersonnelID = p.PersonnelID
INNER JOIN TranType AS tt
    ON t.tranTypeID = tt.TranTypeID
JOIN #Temp tempt on tempt.tranDescription = tt.TranDescription
GROUP BY name,tt.TranDescription;
**************************************************************

And of course the problem here is that I would need to know how many columns 
there are to do the Row test.

Tom
"tshad" <toms@pdsa.com> wrote in message 
news:OFDXQuSfKHA.1112@TK2MSFTNGP04.phx.gbl...
> This was why I was trying to use multiple ways of handling the system 
> where I use a VIEW to call a Function and/or a Function to call a Stored 
> procedure.
>
> The problem is I run into a gotcha, such as you can't call a Stored 
> procedure in a function.
>
> My problem is that I don't know what the possible sums are.  This was what 
> we did before with the SUMs.  The problem is I don't know what the names 
> would be which is why I was trying to use a temp table and use the temp 
> table in some way in a function.  Then use that with actual Select 
> statement but can't find a way to create the column names from the 
> temptables.
>
> It may be that there isn't a way.  My problem is that I have this working 
> fine as a Stored Procedure but can't call it from a Function or a View and 
> I have to end up with a View that is called by the Report Engine.
>
> Thanks,
>
> Tom
>
> "Tom Cooper" <tomcooper@comcast.net> wrote in message 
> news:u46TfkSfKHA.1592@TK2MSFTNGP06.phx.gbl...
>> As I understand it, you want to return a result set, and you want at run 
>> time to be able to vary the number and/or names of the columns that are 
>> returned.  You cannot do that with a view or a function.  The reason is 
>> that the query optimizer must be able to determine the names, number, and 
>> properties of the columns returned by the view or function before the 
>> view or function is called.  So the names, number, and properties of the 
>> columns to be returned are fixed when you do the CREATE VIEW or CREATE 
>> FUNCTION.
>>
>> One possible workaround is to create a single view that returns all the 
>> possible desired sums.  Then your reporting software just gets the 
>> columns needed for the current report.  Depending on exactly what you are 
>> doing you may be able to do this without incurring a severe perofrmance 
>> penalty. Most of the cost of a query is in the retrieval of the rows and 
>> sorting and grouping them.  The cost of doing sums and CASE expressions 
>> is often comparitively very small.  So, for example, you may find that 
>> the following two views give you essentially the same performance:
>>
>> SELECT P.name,
>>        SUM(CASE WHEN tt.TranDescription = 'Clicked' THEN CAST([count] AS 
>> INT) ELSE 0 END) AS clicked_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;
>>
>> and
>>
>> 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;
>>
>> Tom
>>
>> "tshad" <toms@pdsa.com> wrote in message 
>> news:etPTUMSfKHA.2780@TK2MSFTNGP05.phx.gbl...
>>> Actually what I was doing was doing something like what Plamen mentioned 
>>> using the dynamic sql mentioned below to create the sum statements.
>>>
>>> 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;
>>>
>>> The problem is that I can't use EXEC or DECLARE in a View.   I then 
>>> thought about doing the whole thing and calling it from a function, but 
>>> I can't execute a Stored Procedure from a function.
>>>
>>> I was thinking of using a Multi Statement Function that would use a 
>>> couple of SELECTs to solve the problem
>>>
>>> I am now looking at taking this statement and instead of putting the 
>>> variable names in a variable as I do below, do something like:
>>>
>>> drop table #Temp
>>>
>>> Select tranDescription into #temp
>>> 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;
>>>
>>> select * from #Temp
>>>
>>> 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;
>>>
>>> Somehow replacing the CASE WHEN tt.TranDescription = 'Clicked'  with 
>>> something that refers to the #Temp table.
>>>
>>> Not sure if this can be done.
>>>
>>> Tom
>>>
>>> Now I have table with my column names.  But I can't think of how to use 
>>> a SELECT with a Join on this temptable to
>>> "tshad" <toms@pdsa.com> wrote in message 
>>> news:egf0AlRfKHA.4636@TK2MSFTNGP04.phx.gbl...
>>>> 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@sommarskog.se> wrote in message 
>>>> news:Xns9CE07C4EFC109Yazorman@127.0.0.1...
>>>>> tshad (tfs@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@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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>
> 


0
tshad
12/15/2009 3:16:25 AM
tshad (toms@pdsa.com) writes:
> 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. 
 
It's an impossible requirement. A view, just like a table, has a fixed 
number of columns with static names and data types.

You will have to talk with your management/client that what they are asking
for cannot be done with the current platform. 

The best you can do from an SQL Server perspective is to write a multi-
statement function that returns a fixed number of columns with fixed names,
and the report tool gets what it gets.

Or they need to change/tweak the report tool so it can call a stored 
procedure.

-- 
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
0
Erland
12/15/2009 8:42:25 AM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9CE262BF3DD49Yazorman@127.0.0.1...
> tshad (toms@pdsa.com) writes:
>> 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.
>
> It's an impossible requirement. A view, just like a table, has a fixed
> number of columns with static names and data types.
>

I agree.

I have not been able to solve the issue nor has anyone else I have talked to 
and have discussed it with the client to have him add the option of handling 
a SP, which would make this a snap.


> You will have to talk with your management/client that what they are 
> asking
> for cannot be done with the current platform.
>
> The best you can do from an SQL Server perspective is to write a multi-
> statement function that returns a fixed number of columns with fixed 
> names,
> and the report tool gets what it gets.
>
Since we have no way of knowing this now or in the future, this wouldn't 
work.

> Or they need to change/tweak the report tool so it can call a stored
> procedure.

That was what was decided.

Thanks,

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


0
tshad
12/16/2009 4:45:19 PM
tshad (tfs@dslextreme.com) writes:
>> The best you can do from an SQL Server perspective is to write a multi- 
>> statement function that returns a fixed number of columns with fixed 
>> names, and the report tool gets what it gets. 
>>
> Since we have no way of knowing this now or in the future, this wouldn't 
> work.

At least not well.
 
>> Or they need to change/tweak the report tool so it can call a stored
>> procedure.
> 
> That was what was decided.
 
That seems like the best decision.


-- 
Erland Sommarskog, SQL Server MVP, esquel@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

0
Erland
12/16/2009 10:57:43 PM
Reply:

Similar Artilces:

Subforms not Displaying
I recently converted an application from an .mdb to a .mdb with linked tables (SQL Server). On my main form, I have several subforms. These subforms show up fine when I'm dealing with existing records, however when I add a new record to the form, the subforms disappear. Any ideas what could cause this? Thanks, Ryan This may help. These subforms all consist of a single combo box. When I open the subform in a new window and switch to Form View (rather than Design View), I get the blank grey background - which would make it appear that the subforms are "disappeared"...

Report that display percentage below certain level
I got a spreadsheet contain inventory number from remote site. I need to run a report to display when inventory below a certain percentage. I try to use privot table to display the infomration. But it doesn't display everything below 40%. Is there anyway you can enter an percentage and display all remote site that below certain percentage (Eample: like 35%) on a report? Thanks. TSS, A pivot table groups like items, and makes a calculation on each group -- not what you need. You need a filter (Excel's way of saying "query" in database parlance). If your table...

WebBrowser Control Displayed Font Size.
In Windows Explorer 7 a menu option is available to change the displayed font size. "View" > "Text Size". Several options are available; "Largest", "Larger", "Medium" etc. Is the functionality available to programmatically adjust the displayed font size in the WebBrowser control? The container for the control is in a Windows form. ...

Displaying a percentage
Sorry if this appears to be a newbie question, but how do I display percentage figure of another cell. Example: A1 �52.89 A2 17.5% of A1 Thank yo -- Timeferre ----------------------------------------------------------------------- Timeferret's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1917 View this thread: http://www.excelforum.com/showthread.php?threadid=57146 ============================================================= About percentages in Excel Niek Otten, July 26 2006 In Excel, percentages are stored as fractions; 15% i...

State table lookup
Hi I have a form for entering Customer data. When entering a new record, I have a lookup combo box to validate the state that the user enters. The combo box displays the state code and the state name. (ie. Ca and California). The state code is the bound value and the state name is the display value. The only time I want to display the full state name is when the user is entering data into the form. I want the state code to be displayed on queries, forms, and reports. Is there a way to do this ? Thanks in advance Mark Create a table with 2 fields like this: StateID Text (say...

Wrong(?) calculation result in Excel
While my calculator gives the correct result for 111,111,111 X 111,111,111 (12345678987654321, when I use Excel for the same calculation, the result I get is close, but not exact--12345678987654300. Can anyone help explain what's happening in Excel? Many Thanks, Jim Hi Go to Excel help and do a search for Specifications. In there you will see that Excel only calculates to 15 digits of precision. I'm sure others will post quoting the IEE specification that Excel works to. It's very interesting and well worth a read. -- Andy. "Jim" <jlclemen@ius.edu> wrote ...

Add-ons and verticals
Hello CRM guru's, We publish a website on which we try to give an overview of all add- ons and verticals that are available for Microsoft CRM. We do have over 100 add-ons listed, but only a few verticals. Do you have any vertical that you want to share with the CRM community? Don't hesitate and post them on www.pimpmycrm.com via the comment form or via the emailadres that is listed there. This is a free website, so you have nothing to lose. Of course, if you are looking for extra functions, add-ons or verticals, you can also find them there. I have few, can you listed that on your si...

Bitmaps' Page Display
How would small bitmaps be displayed on a page of a tab control. The bitmaps would need to be printed out similarly to text, line by line. Each line would contain at least ten small bitmaps.And there could be enough lines to continue past the end the visible tab page. Then a scroll control would be used. Is it possible to do this type of thing. ...

the number "3" displays like an exposant
je ne comprend pas pourquoi le chiffre 3 apparait comme un exposant dans mon classeur ...

Automatic display positive or negative
Dear All, pls help me. when I type $ 52.00 on C1, I want C1 display positive $52 or negative ($ 52) , if B1 is positive or negative, I want the entire column C. Exemple : C1 : I type $52, it display for me $52 , because B1 is positive C2 : I type $52, it display for me ($52) , because B2 is negative B C B1 5 $52 B2 (3) ($52) It is possible ? Thank you for your help. This can only be done with VBA and the code below will do it for you. To use the code, open the workbook and go to the sheet you want this to work on and then: Right-click o...

Display public variable in Header
I have page breaks when a variable changes. How can I display what that variable is in the header? Thank you in advance God bless you ...

wrong display name
Why is my messenger displaying my name from web from my Live profile??? I've never checked any box that allows to take name from web. I want to use my custom name that I set in my messenger client. And if I wanted to change my name in Live profile in home.live.com then it refuses to accept special characters that I've been using for years in my messenger client. There is something broken, you know, and please fix it as soon as possible. Thanks! Greetings, There are instances where it can change your name to what's in your live profile in Messenger, but you c...

Displaying Total Word Count on Cover Sheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I want to display/show the Total Word count of my document on the Cover Page, when I print my document is is there on the Cover Page. How can I do that? Is there an Auto Text or something like that that I can use? Please anyone reply please. You can use Insert> Field to insert the NumWords Field from the Document Information category. However, the field does not update automatically. You need to update it manually by clicking in the field & pressing F9, by Control/Right-Clicking the Field & selecting...

Automating Different Survey Results
Does anyone have any idea on how to facilitate the preparation of surve results? Our survey results are downloaded from our survey online facility (som facility when it can't even produce the report :sigh:). These survey are about the services of the different support groups of our company So each survey has its own set of questions. The backend process o translating these results is quite tedious and I've been thinking o how to automate it ... I know that there is a better way. I am usin Excel but only the standard features such as countif, sumproduct pivot, charts. But still it c...

Force TRUE in a check box as result from an option button??
I have a set of option buttons and a set of check boxes. One of the check boxes is for a certain value that is similar to one of the option buttons. Essentially, what I want is for that check box to automatically go to a "TRUE" (checked) state when the certain option button is selected. When the other option buttons are selected, the check box will remain in it's normal state which will allow the user to select it or not depending on other variables. But, if the option button is on that certain selection, I would like to make it so that check box MUST remain on TRUE, and ...

Display in folder list
We are running Exchange 2000 SP3 with Outlook 2002 clients. We have several associates that need access to other mailboxes. When you add these mailboxes into the folder list view, they are showing up after the Public Folders. We have also seen it where there you open up the Outlook client and the associate's mailbox is displaying after Public Folders instead of before. What is causing this to occur? ...

Excel does not display entered values correctly
I am currently running Excel 2000 on WinXP Pro. Excel does not display the correct values in cells when they are typed. The program arbitrarily assigns a decimal value to whatever number is typed. For example, if "1111111111" is entered, it is displayed as "11111.11111" I have gone through cell formatting, detect and repair and other options, but none have worked. The program use to work fine. It started this recently and it won't go away. If it helps, when a number is typed in another program, i.e. notepad, and copied and pasted into Excel the value displ...

Urgent Help
All, Sorry but need some quick help in this project. I was told to create a quartile chart for last one year of closing price of Soybean Commodity. I am going to paste small data set here. Can someone please guide how to show quartiles as vertical lines in a chart? Also someone please suggest what should be X axis and Y axis values here? Date Close Price 1/1/2009 38.36 Quartile 1 60.7825 1/2/2009 43.4 Quartile 2 67.27 1/3/2009 45.34 Quartile 3 76.9675 1/4/2009 46.49 1/5/2009 49.12 1/6/2009 49.5 1/7/2009 50.5 1/8/2009 51.13 1/9/2009 51.8 1/10/2009 51.86 1/11/20...

Global Address List (It's not displayed)
Hi WHEN I CLICK ON "TO" OR "CC" I GET A MESSAGE SAYING "THE ADDRESS LIST COULD NOT BE DISPLAYED. THE OPERATION CANNOT BE PERFORMED BECAUSE THE CONNECTION TO THE SERVER IS OFFLINE." What could i do ?? Thanks Hi Johan, did you work with Exchange? Or only with a lokal Profile? -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "Johan" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:2cf6c01c469be$c0d00ee0$a501280a@phx.gbl... > Hi ...

How to freeze one column vertically, one horizontal in same sprdsh
Is it possible to freeze a column vertically and one horizontally in the same spreadsheet? Do you mean other than Row 1 and Column 1? If that is what you meant, then the answer is "no". -- Rick (MVP - Excel) "sweaver" <sweaver@discussions.microsoft.com> wrote in message news:15D0A612-C384-4EC7-8553-A8CEC509AFC1@microsoft.com... > Is it possible to freeze a column vertically and one horizontally in the > same > spreadsheet? What cell is below the row you want frozen and to the right of the column you want frozen. Find that inter...

random incomplete query results
Hi to all, on a form I'm filling a "box" (I don't know the english name, is like a combo without the input selection row) from a table on the same database. To do that, I'm using the property "RowSource", also to change the ordering field. The problem is that, when I'm using the database on a PC with Windows 2000 and Office 2000, "sometime" (exactly! not all the times!) the list on the box is covering only a fraction of the entire table! After some trials I get the full list... The same database, on my PC (XP and Office 2003), is working alw...

Is it possible to display a CView in a Cdialog
Hi, Would it be possible to insert a CView in a CDialog. I explain I have a CDialog based app with a CStatic control (IDC_CARD_GOES_HERE) inside and I am loading a Cwnd control for now like this : CRect CardRect; CWnd* pWnd = GetDlgItem(IDC_CARD_GOES_HERE); ASSERT(pWnd); pWnd->GetWindowRect(CardRect); ScreenToClient(CardRect); m_editor.Create( WS_CHILD | WS_VISIBLE, CardRect, this, &m_objs ); // Create the control over CStatic But now I have a CView instead of my Cwnd and could it be possible to load it ? Yes it can be done, but why would you want to do it? Why not insert the c...

Function to control how data displayed
I have 2 columns that I exported from Access to Excel. In Access th columns were Yes or No. In excel they display as True or False. I wan them to show as Yes or No what is the function to make this happen? Thank you! -- LOgle531 ----------------------------------------------------------------------- LOgle5318's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2659 View this thread: http://www.excelforum.com/showthread.php?threadid=39860 LOgle5318, Assuming your True and False are in columns A & B then In C1 put =IF(A1="True","Yes") and...

- Why can't I change display names in Hotmail?
There's no Hotmail group, so I thought I'd try asking here. When I try changing my display name in Hotmail, and hit SAVE at the bottom.. it just reloads the Account Information form (with the changes). It doesn't go to a success page. When I try sending email, the old display name is still there. When I log out and log back in and go into my personal profile options, the old display name is back. What am I doing wrong? Contact MSN Hotmail Support http://support.msn.com/contactus_emailsupport.aspx?productkey=hotmail&ct=eformts -- Carey Frisch Microsoft MVP Windows X...

Can anyone get the front and rear pages of a booklet to display?
I can merrily paste a pic onto facing INTERNAL pages of a booklet. But I can't display "facing pages" for the covers (I can print preview)... I can't get a picture to go onto the front and rear covers, the picture gets cropped to the spine. I want one picture that wraps around the covers of the booklet (4" x 8 1/2") to fit into a #10 envelope. This is kind of tricky. The best way to achieve this would be doing the procedure on an inside facing page so you can readily see what you are doing. Insert the picture, copy/paste the picture, stack the two images b...