Converting rows in a related table to columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Converting rows in a related table to columns

Author

Topic

lkelly
Starting Member

2 Posts

Posted - 2014-10-06 : 13:24:04

Sorry for the sketchy subject line, but here's my situation...

I have an articles table with a basic layout of (tblArticle):

Article_ID, Title, Body

I have a view tracking table with this layout (tblTracking):

Tracking_ID, Tracking_Type

Finally, I have a table where I store the article views for each tracking type (tblMapping):

Article_ID, Tracking_ID, Views

Note that the primary key for tblMapping is a combination of Article_ID and Tracking_ID.

I insert a row into tblMapping each time an article is viewed with a certain tracking, or if the row exists I increment the view count. I might end up with:

tblArticle


Article_ID   Title    Body
1            foo      foobody
2            bar      barbody
3            blah     blahbody
4            junk     junkbody

tblTracking


Tracking_ID   Tracking
1             Web
2             Email
3             Mobile

tblMapping


Article_ID   Tracking_ID   Views
1            1             10
1            2             4
2            2             12
3            1             22
3            3             8

What I need is a query that will provide 0 Views for each article when there is no entry in the tblMapping table. I'd be creating a column in the output for each row in tblTracking. Something like this:


Article_ID    Title   Web   Mobile   Email
1             foo     10    4        0
2             bar     0     12       0
3             blah    22    0        8
4             junk    0     0        0

I can generate this by looping through webs erver code and running through a bunch of queries, but that seems very inefficient. If it were possible to do this in one query, that would be great. I should note that I'd rather not have to hard code the tracking types (and number of entries in the tblTracking table), but instead just dynamically loop through them. Maybe the column names aren't the values from tblTracking.Tracking, but are instead some placeholder like Tracking1, Tracking2, Tracking3.

Any thoughts? Hopefully this is clear enough.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-06 : 15:35:28

Using PIVOT:





declare @tblArticle table (Article_ID int , Title varchar(50), Body varchar(50))
declare @tblTracking table (Tracking_ID int , Tracking_Type varchar(50))
declare @tblMapping  table( Article_ID int , Tracking_ID int , Views int)

insert into @tblArticle (Article_ID, Title, Body) values
(1,            'foo',      'foobody'),
(2,            'bar' ,     'barbody'),
(3,            'blah',     'blahbody'),
(4,            'junk',    'junkbody')

insert into @tblTracking (Tracking_ID,   Tracking_Type) values
(1,             'Web'),
(2,             'Email'),
(3,             'Mobile')

insert into @tblMapping (Article_ID,   Tracking_ID,   Views) values
(1,            1,             10),
(1,            2,             4),
(2,            2,             12),
(3,            1,             22),
(3,            3,             8)

;with src as (
	select a.Article_ID, a.Title, t.Tracking_Type, m.Views
	from @tblArticle a
	join @tblMapping m on a.Article_ID = m.Article_ID
	join @tblTracking t on m.Tracking_ID = t.Tracking_ID
	)
select Article_ID, Title, isnull(Web,0) Web, isnull(Email,0) Email, isnull(Mobile,0) Mobile
from src
pivot(sum(views) for Tracking_type in (Web,Email,Mobile)) pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources