SQLTeam.com | Weblogs | Forums

Output data overview?

Afternoon all,

Please forgive me I'm not sure what the terminology would be, I'm wondering if its possable to output a table (not the SQL meaning of table) that gives an overview of data. For instance, from this example data :

CREATE TABLE Forumtest (
						T_ID INTEGER,
						T_Firstname VARCHAR(30),
						T_Lastname VARCHAR(30),
						T_Status VARCHAR(30),
						T_Source VARCHAR(30)
						)
						
INSERT INTO Forumtest (T_ID, T_Firstname, T_Lastname, T_Status, T_Source) VALUES
(1000, 'Jane', 'Doe', 'Live', 'Website'),
(1001, 'John', 'Doe', 'Live', 'Word Of Mouth'),
(1002, 'Tina', 'Yip', 'Live', 'Website'),
(1003, 'Dave', 'Liu', 'Live', 'Facebook'),
(1004, 'Ryan', 'Hay', 'Compliance', 'Unknown'),
(1005, 'Matt', 'See', 'Complaince', 'Word Of Mouth'),
(1006, 'Josh', 'Tob', 'Compliance', 'Website'),
(1007, 'Stef', 'Rae', 'FNR', 'Linked In'),
(1008, 'Ruby', 'Ray', 'FNR', 'Other'),
(1009, 'Soph', 'Bob', 'Pending', 'Other'),
(1010, 'Tomo', 'Tut', 'Pending', 'Website'),
(1011, 'Bill', 'Tuk', 'Booked', 'Website'),
(1012, 'Char', 'Pou', 'Booked', 'Website')

You would be able to output something like :

I know i can use 'Group By' to out put the first part but I can't think how to get the rest....

Hope that makes sence...

Thanks

Dave

Pivot is your friend

Work example, getting to your final output

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select * from pivoted
;

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
;

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
union ALL
select null, sum([live]), sum([compliance]), sum([FNR]), sum([Pending]), sum([Booked]), sum([live]) from pivoted
;
1 Like
SELECT 
    COALESCE(Source, 'Totals') AS Source,
    Live, Compliance, FNR, Pending, Booked, Total
FROM (
    SELECT 
        T_Source AS Source,
        SUM(CASE WHEN T_Status = 'Live' THEN 1 ELSE 0 END) AS Live,
        SUM(CASE WHEN T_Status = 'Compliance' THEN 1 ELSE 0 END) AS Compliance,
        SUM(CASE WHEN T_Status = 'FNR' THEN 1 ELSE 0 END) AS FNR,
        SUM(CASE WHEN T_Status = 'Pending' THEN 1 ELSE 0 END) AS Pending,
        SUM(CASE WHEN T_Status = 'Booked' THEN 1 ELSE 0 END) AS Booked,
        SUM(1) AS Total
    FROM Forumtest
    GROUP BY T_Source WITH ROLLUP
) AS query1
ORDER BY CASE WHEN Source IS NULL THEN 1 ELSE 0 END, Source

Interesting....

See fully worked examples below (I've fixed the totaling issue in my example, plus added another one using a group by with rollup and added @ScottPletcher's example

And they are different (by 1) (look at the "Word of Mouth" row in the final two outputs)

Why?

Because of the data...

"Compliance" vs "Complaince"...

You need to decide which output is "correct"!

CREATE TABLE #Forumtest (
						T_ID INTEGER,
						T_Firstname VARCHAR(30),
						T_Lastname VARCHAR(30),
						T_Status VARCHAR(30),
						T_Source VARCHAR(30)
						)
						
INSERT INTO #Forumtest (T_ID, T_Firstname, T_Lastname, T_Status, T_Source) VALUES
(1000, 'Jane', 'Doe', 'Live', 'Website'),
(1001, 'John', 'Doe', 'Live', 'Word Of Mouth'),
(1002, 'Tina', 'Yip', 'Live', 'Website'),
(1003, 'Dave', 'Liu', 'Live', 'Facebook'),
(1004, 'Ryan', 'Hay', 'Compliance', 'Unknown'),
(1005, 'Matt', 'See', 'Complaince', 'Word Of Mouth'),
(1006, 'Josh', 'Tob', 'Compliance', 'Website'),
(1007, 'Stef', 'Rae', 'FNR', 'Linked In'),
(1008, 'Ruby', 'Ray', 'FNR', 'Other'),
(1009, 'Soph', 'Bob', 'Pending', 'Other'),
(1010, 'Tomo', 'Tut', 'Pending', 'Website'),
(1011, 'Bill', 'Tuk', 'Booked', 'Website'),
(1012, 'Char', 'Pou', 'Booked', 'Website')


select * from #Forumtest

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked]
from pivotdata 
pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p;


; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select * from pivoted
;

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
;

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
union ALL
select 'total', sum([live]), sum([compliance]), sum([FNR]), sum([Pending]), sum([Booked]), sum([live] + [compliance] + [FNR] + [Pending] + [Booked] ) from pivoted
;

; with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select isnull(t_source, 'total'),  sum([live]) as live, sum([compliance]) as [compliance], sum([FNR]) as [FNR], sum([Pending]) as [Pending], sum(Booked) as [Booked], sum([live] + [compliance] + [FNR] + [Pending] + [Booked]) as total from pivoted
group by t_source with ROLLUP

SELECT 
    COALESCE(Source, 'Totals') AS Source,
    Live, Compliance, FNR, Pending, Booked, Total
FROM (
    SELECT 
        T_Source AS Source,
        SUM(CASE WHEN T_Status = 'Live' THEN 1 ELSE 0 END) AS Live,
        SUM(CASE WHEN T_Status = 'Compliance' THEN 1 ELSE 0 END) AS Compliance,
        SUM(CASE WHEN T_Status = 'FNR' THEN 1 ELSE 0 END) AS FNR,
        SUM(CASE WHEN T_Status = 'Pending' THEN 1 ELSE 0 END) AS Pending,
        SUM(CASE WHEN T_Status = 'Booked' THEN 1 ELSE 0 END) AS Booked,
        SUM(1) AS Total
    FROM #Forumtest
    GROUP BY T_Source WITH ROLLUP
) AS query1
ORDER BY CASE WHEN Source IS NULL THEN 1 ELSE 0 END, Source

hi

similar to Scotts solution
i have added order by of T-Source Column

hope it helps
:slight_smile: :slight_smile:

drop create data ..
CREATE TABLE Forumtest (
						T_ID INTEGER,
						T_Firstname VARCHAR(30),
						T_Lastname VARCHAR(30),
						T_Status VARCHAR(30),
						T_Source VARCHAR(30)
						)
						
INSERT INTO Forumtest (T_ID, T_Firstname, T_Lastname, T_Status, T_Source) VALUES
(1000, 'Jane', 'Doe', 'Live', 'Website'),
(1001, 'John', 'Doe', 'Live', 'Word Of Mouth'),
(1002, 'Tina', 'Yip', 'Live', 'Website'),
(1003, 'Dave', 'Liu', 'Live', 'Facebook'),
(1004, 'Ryan', 'Hay', 'Compliance', 'Unknown'),
(1005, 'Matt', 'See', 'Compliance', 'Word Of Mouth'),
(1006, 'Josh', 'Tob', 'Compliance', 'Website'),
(1007, 'Stef', 'Rae', 'FNR', 'Linked In'),
(1008, 'Ruby', 'Ray', 'FNR', 'Other'),
(1009, 'Soph', 'Bob', 'Pending', 'Other'),
(1010, 'Tomo', 'Tut', 'Pending', 'Website'),
(1011, 'Bill', 'Tuk', 'Booked', 'Website'),
(1012, 'Char', 'Pou', 'Booked', 'Website')
SQL ...
SELECT t_source, 
       Sum(CASE 
             WHEN t_status = 'Live' THEN 1 
             ELSE 0 
           END) AS Live, 
       Sum(CASE 
             WHEN t_status = 'Compliance' THEN 1 
             ELSE 0 
           END) AS Compliance, 
       Sum(CASE 
             WHEN t_status = 'FNR' THEN 1 
             ELSE 0 
           END) AS FNR, 
       Sum(CASE 
             WHEN t_status = 'Pending' THEN 1 
             ELSE 0 
           END) AS Pending, 
       Sum(CASE 
             WHEN t_status = 'Booked' THEN 1 
             ELSE 0 
           END) AS Booked, 
       Sum(CASE WHEN t_status ='Live' THEN 1 ELSE 0 END) + Sum(CASE WHEN 
       t_status = 
       'Compliance' THEN 1 ELSE 0 END) + Sum(CASE WHEN t_status = 'FNR' THEN 1 
       ELSE 0 
       END) + Sum(CASE WHEN t_status = 'Pending' THEN 1 ELSE 0 END) + Sum(CASE 
       WHEN 
       t_status = 'Booked' THEN 1 ELSE 0 END) 
FROM   forumtest 
GROUP  BY rollup( t_source ) 
ORDER  BY CASE 
            WHEN t_source = 'Website' THEN 1 
            WHEN t_source IS NULL THEN 3 
            ELSE 2 
          END ASC 

go
Result

image

Here you need to create a view table with the help of view table you can find another out put.

Afternnon,

Wow, thank you so much guys, I wasn't expecting expecting completed answers :smiley:

Yes my bad I can't spell 'Compliance' in the example :blush:

I've spent a little bit of time going through the different solutions, the one thing i've realised is how much SQL i don't know!! Are there any good examples / tutorials of pivot tables, its not something i've ever dabbled in and looking at how powerful they are I think I should...!

The solution I like, probably because it makes the most sence to me is :

with pivotdata as (
	select
		t_source
		, t_status
		, 1 as d
	from #Forumtest
)
, pivoted as (
	select t_source, [live], [compliance], [FNR], [Pending], [Booked]
	from pivotdata 
	pivot(count(d) for t_status in ([live], [compliance], [FNR], [Pending], [Booked])) as p
)
select t_source, [live], [compliance], [FNR], [Pending], [Booked], [live] + [compliance] + [FNR] + [Pending] + [Booked] as [total] from pivoted
union ALL
select 'total', sum([live]), sum([compliance]), sum([FNR]), sum([Pending]), sum([Booked]), sum([live] + [compliance] + [FNR] + [Pending] + [Booked] ) from pivoted

Still trying to get my head around how & why it works; but.... if I wanted to add a field to it (obviously adding it to the example data too, i just bashed in random number between 0 & 25) eg...

	select
	      t_source
	      , t_status 
              , t_daysworked
	      , 1 as d
	from #Forumtest

I'm just trying to work out where else I'd need to reference it, I thought it was self explanitory but i doesn't seem to like it..... Hmmmmm leave this one with me.......

Thanks

Dave

hi Dave

your comment == I'm just trying to work out where else I'd need to reference it
for this part use ctrl+f ... find all occurences

if you google search for PIVOT ... there are lots of youtube videos
a notebook ... pen ..taking NOTES makes it every easy !!!!

for example


hope it helps
:slight_smile: :slight_smile:

You didn't "add" an order by of source, since my original code ordered by source already.

My bad Scott
I made a mistake

I thought your SQL was not doing it
When I ran it

I was trying to order by
In the sequence in the diagram
That Dave had with website being first

,:+1::+1:

Scott

I was trying to get the Source to order by like in this diagram
given by Quattro Dave