Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

trying to create a table - unpivot Expand / Collapse
Author
Message
Posted Tuesday, October 08, 2013 8:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 11:20 PM
Points: 49, Visits: 91
Hi ,

I am having problems understand the function unpivot on sql.
I have a query:
select RecordDay, ClickCount into #myclicks from dbo.clicks
where RecordDay >= getdate()-38


that the outcome will be (with the hours, minutes and seconds)
RecordDay ClickCount
8/31/2013 1
8/31/2013 1
8/31/2013 6
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 1
8/31/2013 2

I want to build from my temp table something that will look like this, where the measure will be the sum of ClickCount
RecordDay 8/31/2013 9/7/2013 9/14/2013 9/21/2013
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

I know I will need to define on the columns
 datepart(weekday,getdate()) = datepart(weekday,datecreated) 


in order to get the same day of the week, and I imagine I will need to do a dateadd to group it by minutes on the rows.
But I am getting extremely lost here.
Help please
Post #1502639
Posted Tuesday, October 08, 2013 8:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
Your post is lacking any level of detail required for anybody to understand the problem, let alone the question. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502648
Posted Tuesday, October 08, 2013 8:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 11:20 PM
Points: 49, Visits: 91
hi,

please find the sample requested

CREATE TABLE clicks (RecordDay datetime, ClickCount INT)
GO
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-10-05 13:23:44, 2)
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-10-05 15:45:21, 6)
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-10-05 16:45:21, 1)
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-10-05 17:45:21, 12)
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-10-04 18:45:21, 3)
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-10-03 19:45:21, 24)
INSERT INTO clicks (RecordDay datetime, ClickCount INT)
VALUES(2013-11-02 20:45:21,3)

Post #1502657
Posted Tuesday, October 08, 2013 8:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
Excellent. Now what I don't understand is what do you want from this sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502659
Posted Tuesday, October 08, 2013 8:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
For anybody else coming along here is the sample data in a format that is consumable.

CREATE TABLE clicks (RecordDay datetime, ClickCount INT)
GO
INSERT INTO clicks (RecordDay, ClickCount)
select '2013-10-05 13:23:44', 2 union all
select '2013-10-05 15:45:21', 6 union all
select '2013-10-05 16:45:21', 1 union all
select '2013-10-05 17:45:21', 12 union all
select '2013-10-04 18:45:21', 3 union all
select '2013-10-03 19:45:21', 24 union all
select '2013-11-02 20:45:21',3



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502661
Posted Tuesday, October 08, 2013 9:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 11:20 PM
Points: 49, Visits: 91
i am trying to get
on the columns the dates
on the rows the hours (from those dates)
and the sum of the clicks as a measure.

the same i gave has a few lines but the original table has over a million.

i will keep in mind your notes on how to post.

thanks for the help
Post #1502672
Posted Tuesday, October 08, 2013 9:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:24 PM
Points: 11,990, Visits: 11,007
astrid 69000 (10/8/2013)
i am trying to get
on the columns the dates
on the rows the hours (from those dates)
and the sum of the clicks as a measure.

the same i gave has a few lines but the original table has over a million.

i will keep in mind your notes on how to post.

thanks for the help


I am still a little bit confused as to what you want as output. Can you provide the details of your desired output based on the sample data you provided?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1502681
Posted Tuesday, October 08, 2013 9:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:14 PM
Points: 2,763, Visits: 5,912
Here's something for you to start with. To understand this method called CROSS TABS, you could visit this article: http://www.sqlservercentral.com/articles/T-SQL/63681/
To learn how to make it dynamic, use part 2: http://www.sqlservercentral.com/articles/Crosstab/65048/

WITH DayHours AS(
SELECT *
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0))x(ihour)
)
SELECT ihour,
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-02' THEN ClickCount ELSE 0 END) AS [2013-10-02],
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-03' THEN ClickCount ELSE 0 END) AS [2013-10-03],
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-04' THEN ClickCount ELSE 0 END) AS [2013-10-04],
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) = '2013-10-05' THEN ClickCount ELSE 0 END) AS [2013-10-05]
FROM clicks
RIGHT JOIN DayHours ON DATEPART( HH, RecordDay) = ihour
GROUP BY ihour




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502687
Posted Tuesday, October 08, 2013 11:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 11:20 PM
Points: 49, Visits: 91
Thanks for your help.

I did a few changes on the query, but i am still encountering a few problems.
it is not summing the clickcount, i do get a three dimensional table like a wanted but without any measures
and also the table is dynamic and i dont know how to make the name of the columns dynamic where it will show the date it is checking.


WITH DayHours AS(
SELECT *
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0))x(ihour)
)
SELECT ihour,
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) >= getdate()-21 and CONVERT(char(10), RecordDay, 120) < getdate()-22 THEN ClickCount ELSE 0 END) AS [2013-10-02],
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) >= getdate()-14 and CONVERT(char(10), RecordDay, 120) < getdate()-15 THEN ClickCount ELSE 0 END) AS [2013-10-03],
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) >= getdate()-7 and CONVERT(char(10), RecordDay, 120) < getdate()-8 THEN ClickCount ELSE 0 END) AS [2013-10-04],
SUM( CASE WHEN CONVERT( char(10), RecordDay, 120) >= getdate() THEN ClickCount ELSE 0 END) AS [2013-10-05]
FROM clicks
RIGHT JOIN DayHours ON DATEPART( HH, RecordDay) = ihour
GROUP BY ihour
order by ihour asc


sql can be so hard on me sometimes....
Post #1502734
Posted Tuesday, October 08, 2013 11:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 11:20 PM
Points: 49, Visits: 91
p.s. if someone can tell me how to post a table, i will gladly draw the results
Post #1502736
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse