Display results horizontal and not vertical

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

From: tshad on 8 Dec 2009 20:21

---

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

From: Plamen Ratchev on 8 Dec 2009 21:31

---

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

From: tshad on 9 Dec 2009 12:20

---

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(a)SQLStudio.com> wrote in message
news:sM-dnZHDHsR0loLWnZ2dnUVZ_vZi4p2d(a)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

From: tshad on 9 Dec 2009 16:00

---

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(a)SQLStudio.com> wrote in message
news:sM-dnZHDHsR0loLWnZ2dnUVZ_vZi4p2d(a)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

From: tshad on 9 Dec 2009 16:07

---

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(a)pdsa.com> wrote in message
news:uNeNdKReKHA.2164(a)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(a)SQLStudio.com> wrote in message
> news:sM-dnZHDHsR0loLWnZ2dnUVZ_vZi4p2d(a)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
>
>

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