CODEWITHSUNDEEP
sqlselectforeign-keysrow
CrimsonOp
CrimsonOp

Reputation: 23

how to use SQL table rows as columns for another table

I have one table of activities, one table of users, and a third table linking users to activities using foreign keys.

What I'm trying to do is create a results table that will have the activities as columns and the users as rows with the cells being the number of activities of that type the user participated in.

For example, the columns would be

User | Activity A | Activity B | Activity C

And a user who had done each activity three times would result in a row of

John Doe | 3 | 3 | 3

Now, I can do this easily if I manually add a count() call for each activity in the database like:

select 
    u.name, 
    (select count(*) 
     from userActivity ua 
     where ua.userID = user.userID and ua.activityID = 1), 
    (select count(*) 
     from userActivity ua 
     where ua.userID = user.userID and ua.activityID = 2),
    (select count(*) 
     from userActivity ua 
     where ua.userID = user.userID and ua.activityID = 3) 
from 
    user u

But this doesn't help me if someone enters an Activity D into the system tomorrow. The report wouldn't show it. How can I use the Activity table's rows as columns?

Upvotes: 2

Views: 4239

Answers (2)

Mark Moretto
Mark Moretto

Reputation: 2348

I did a quick query that might help. This uses the Pivot function, which was mentioned before.

You can run the whole thing, or just skip to the bottom!

-- Temp tables
IF OBJECT_ID('tempdb.dbo.#_tmp') IS NOT NULL DROP TABLE #_tmp
IF OBJECT_ID('tempdb.dbo.#_user') IS NOT NULL DROP TABLE #_user
IF OBJECT_ID('tempdb.dbo.#_activity') IS NOT NULL DROP TABLE #_activity
IF OBJECT_ID('tempdb.dbo.#_useractivity') IS NOT NULL DROP TABLE #_useractivity


-- User table
CREATE TABLE #_user (
    [USER_ID] INT IDENTITY(1,1) NOT NULL,
    [FIRST_NAME] NVARCHAR(50)
)
INSERT INTO #_user ([FIRST_NAME])
VALUES ('John'), ('Peter'), ('Paul')


-- Activity table
CREATE TABLE #_activity (
    [ACTIVITY_ID] INT IDENTITY(1,1) NOT NULL,
    [ACTIVITY_NAME] NVARCHAR(255)
)
INSERT INTO #_activity ([ACTIVITY_NAME])
VALUES ('Sailing'), ('Bowling'), ('Hiking')


-- Composite table
CREATE TABLE #_useractivity (
    [LOG_ID] INT IDENTITY(1,1) NOT NULL,
    [USER_ID] INT,
    [ACTIVITY_ID] INT
)
INSERT INTO #_useractivity ([USER_ID], [ACTIVITY_ID])
VALUES (1,1),(1,2),(1,3),(1,3),(2,2),(2,3),(3,1), (3,2),(1,2),(2,1)

-- Main data table.
SELECT USR.FIRST_NAME
, A.ACTIVITY_NAME
INTO #_tmp
FROM #_useractivity AS UA
INNER JOIN #_user AS USR ON USR.USER_ID = UA.USER_ID
INNER JOIN #_activity AS A ON A.ACTIVITY_ID = UA.ACTIVITY_ID
SELECT * FROM #_tmp


-- Use pivot function to get desired results.
DECLARE @_cols AS NVARCHAR(MAX)
DECLARE @_sql  AS NVARCHAR(MAX)
SET @_cols = STUFF((SELECT ',' + QUOTENAME(T.ACTIVITY_NAME)
                    FROM #_tmp AS T
                    GROUP BY T.ACTIVITY_NAME
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

-- Trick is to add 1 "counter" before pivoting.
set @_sql = '
    SELECT Name, ' + @_cols + '
    FROM (
        SELECT FIRST_NAME AS Name, ACTIVITY_NAME, 1 AS COUNT
        FROM #_tmp
    ) AS SRC
    PIVOT (
        SUM(COUNT) FOR ACTIVITY_NAME IN (' + @_cols + ')
    ) p'
EXEC(@_sql)

Main data table:

FIRST_NAME  ACTIVITY_NAME
John        Sailing
John        Bowling
John        Hiking
John        Hiking
Peter       Bowling
Peter       Hiking
Paul        Sailing
Paul        Bowling
John        Bowling
Peter       Sailing

Output:

Name        Bowling  Hiking Sailing
John        2        2      1
Paul        1        NULL   1
Peter       1        1      1

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You seem to want conditional aggregation:

select u.name,
       sum(case when ua.activityID = 1 then 1 else 0 end) as cnt_1,
       sum(case when ua.activityID = 2 then 1 else 0 end) as cnt_2,
       sum(case when ua.activityID = 3 then 1 else 0 end) as cnt_3
from user u left join
     userActivity ua 
     on ua.userID = u.userID 
group by u.name;

Upvotes: 0

Related Questions