CODEWITHSUNDEEP
sqljoinpivot
Barrett Kuethen
Barrett Kuethen

Reputation: 1914

How do I display values from one column onto two columns in an SQL statement?

I have a table like below:

enter image description here

There are multiple WebSiteIDs. I'd like the output to be like below:

Date  , WebSiteID1_Views, WebSiteID2_Views
1/1/12, 91              , 43
1/2/12, 84              , 34

(where SiteUSerID = some userdefined ID)

I'm pretty sure I need to do some sort of Join, but I cannot find the answer on Stack.

Your help is greatly appreciated!

Upvotes: 1

Views: 1707

Answers (3)

Arion
Arion

Reputation: 31249

Maybe something like this:

Test data

CREATE TABLE Table1
        (
            ID INT,
            Date DATETIME,
            SiteUser INT,
            WebSiteID INT,
            WebSiteViews INT
        )
INSERT INTO Table1
VALUES
    (1,'2011-10-06 00:00:00:000',36,1,197),
    (2,'2011-10-06 00:00:00:000',37,1,70),
    (3,'2011-10-06 00:00:00:000',36,1,111),
    (4,'2011-10-06 00:00:00:000',32,1,24),
    (5,'2012-02-26 00:00:00:000',31,1,37),
    (6,'2012-02-26 00:00:00:000',36,1,92),
    (7,'2012-02-26 00:00:00:000',34,1,50),
    (8,'2012-02-26 00:00:00:000',18,1,43),
    (9,'2012-02-26 00:00:00:000',89,1,12),
    (10,'2012-02-15 00:00:00:000',45,2,45),
    (11,'2012-02-15 00:00:00:000',78,2,54),
    (12,'2012-03-24 00:00:00:000',36,2,45),
    (13,'2012-03-24 00:00:00:000',36,2,197),
    (14,'2012-03-24 00:00:00:000',45,2,300),
    (15,'2012-03-24 00:00:00:000',3,2,78),
    (16,'2012-03-24 00:00:00:000',2,2,45),
    (17,'2012-03-24 00:00:00:000',1,2,100),
    (18,'2012-03-24 00:00:00:000',4,2,1)

The unique column names

DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY Table1.WebSiteID ORDER BY Table1.WebSiteID) AS RowNbr,
        CAST(Table1.WebSiteID AS VARCHAR(100)) AS WebSiteID
    FROM
        Table1
)
SELECT  @cols = COALESCE(@cols + ','+QUOTENAME('WebSiteID'+WebSiteID+'_Views'),
                    QUOTENAME('WebSiteID'+WebSiteID+'_Views'))
FROM
    CTE
WHERE
    CTE.RowNbr=1

Dynamic pivot

DECLARE @SiteUser INT=36
DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        convert(varchar(6), Date, 103)+RIGHT(CAST(YEAR(Date) AS VARCHAR(4)),2) AS Date,
        Table1.WebSiteViews,
        ''WebSiteID''+CAST(WebSiteID AS VARCHAR(100))+''_Views'' AS WebSiteID
    FROM
        Table1
    WHERE
        Table1.SiteUser='+CAST(@SiteUser AS VARCHAR(100))+'
) AS p
PIVOT
(
    SUM(WebSiteViews)
    FOR WebSiteID IN ('+@cols+')
)AS pvt'
EXECUTE(@query)

In my case I will drop the temp table:

DROP TABLE Table1

Upvotes: 0

J Cooper
J Cooper

Reputation: 5008

This is the PIVOT way:

SELECT [Date],
       COALESCE([1], 0) AS WebSiteID1_Views,
       COALESCE([2], 0) AS WebSiteID2_Views
FROM (
    SELECT [Date], WebSiteID, WebSiteViews
    FROM @yourTable
    WHERE SiteUSerID = [some userdefined ID]
) AS T
PIVOT(SUM(WebSiteViews) FOR WebSiteID IN([1], [2])) AS P

... And the Non-PIVOT way:

SELECT
    [Date],
    SUM(CASE WebSiteID WHEN 1 THEN WebSiteViews ELSE 0 END) AS WebSiteID1_Views,
    SUM(CASE WebSiteID WHEN 2 THEN WebSiteViews ELSE 0 END) AS WebSiteID2_Views
FROM @yourTable
WHERE SiteUSerID = [some userdefined ID]
GROUP BY Date

Upvotes: 2

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

What you want is a Dynamic Assembled Pivot.

This way, you can have multiple undetermined WebSiteID values and will still work.

I'm out of time right now to write a specific answer to your question, but you can find an analog solution here: https://stackoverflow.com/a/7182489/570191

Just adapt it to your needs

Upvotes: 0

Related Questions