How do I display different values as different columns in SQL?

Question

I have a table with columns UserID, SiteID, SiteViews and another with UserID, First, Last

And I need to display the data as First + Last, Site1Views, Site2Views

There are only 2 site IDs and I need to have the sum of the SiteViews per User.

Currently I have:

select s.UserFirstName + ' ' + s.UserLastName as 'Name', 
j1.SiteViews as 'Site1Name',
j2.SiteViews as 'Site2Name' 
from (Users s INNER JOIN Usage j on s.UserID = j.UserID)

I don't know how to do this. Any help would be appreciated.

What are the primary/foreign key relationships between these tables? — Ryan Gates
– Ryan Gates, Commented Nov 5, 2012 at 23:28
UserID is the relationship being used. There is UsageID on the first table as the primary key as well. — Barrett Kuethen
– Barrett Kuethen, Commented Nov 5, 2012 at 23:38

Abe Miessler · Accepted Answer · 2012-11-05 23:39:32Z

1

Sounds like you might want to use PIVOT for this? I can't test it, but this should get you going int he right direction?

select s.UserFirstName + ' ' + s.UserLastName as Name, 
       [Site1Name],
       [Site2Name]
FROM
       (SELECT s.UserFirstName + ' ' + s.UserLastName,
               SiteName,
               SiteVisits
        from Users s 
        INNER JOIN Usage j1 
          on s.UserID = j1.UserID
       )
PIVOT (SUM(SiteVisits) FOR SiteName IN ([Site1Name],[Site2Name])) as p

edited Nov 5, 2012 at 23:39

answered Nov 5, 2012 at 23:23

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barrett Kuethen Over a year ago

thanks! To be clear, the OtherUsageTable is the same as the first Usage table. i.e. I have UserID 1, SiteID 1, Views 200, UserID 1, SiteID 2, Views 300. Your statement did separate the 2 sites but the UserIDs aren't grouped and the Views are coming across in multiple records still.

Abe Miessler Over a year ago

I updated my answer. Not sure if it will work, but hopefully it will point you in the right direction

bummi · Accepted Answer · 2012-11-05 23:27:35Z

1

how I understood it

select s.UserFirstName + ' ' + s.UserLastName as Name 
,(Select COUNT(*) from SiteViews s1 where s.UserID = s1.UserID /*other condition*/) as SiteView1
,(Select COUNT(*) from SiteViews s2 where s.UserID = s2.UserID /*other condition*/) as SiteView2 
from Users s

answered Nov 5, 2012 at 23:27

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Collectives™ on Stack Overflow

How do I display different values as different columns in SQL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related