Display results horizontal and not vertical

Prev: Parse Name Field into First Name Last Name
Next: statistic data about procedures

From: tshad on 14 Dec 2009 20:08

---

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(a)pdsa.com> wrote in message
news:egf0AlRfKHA.4636(a)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(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
>>
>
>

From: Tom Cooper on 14 Dec 2009 20:51

---

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(a)pdsa.com> wrote in message
news:etPTUMSfKHA.2780(a)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(a)pdsa.com> wrote in message
> news:egf0AlRfKHA.4636(a)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(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
>>>
>>
>>
>
>

From: tshad on 14 Dec 2009 21:08

---

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(a)comcast.net> wrote in message
news:u46TfkSfKHA.1592(a)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(a)pdsa.com> wrote in message
> news:etPTUMSfKHA.2780(a)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(a)pdsa.com> wrote in message
>> news:egf0AlRfKHA.4636(a)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(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
>>>>
>>>
>>>
>>
>>
>

From: tshad on 14 Dec 2009 22:16

---

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(a)pdsa.com> wrote in message
news:OFDXQuSfKHA.1112(a)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(a)comcast.net> wrote in message
> news:u46TfkSfKHA.1592(a)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(a)pdsa.com> wrote in message
>> news:etPTUMSfKHA.2780(a)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(a)pdsa.com> wrote in message
>>> news:egf0AlRfKHA.4636(a)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(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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

From: Erland Sommarskog on 15 Dec 2009 03:42

---

tshad (toms(a)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(a)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

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