Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# PIVOT RESULT IN SQL SERVER 2008R2

Posted on 2013-06-16
Medium Priority
415 Views
Hi

I have 3 columns namely EntryDate,EnteredBy,Boxcodes in a table.  I want to know how much BoxCodes each person (enteredby) has entered in all entrydates.  The following pivot table query doesn't sums up the total for each person for each day.

SELECT 'TotalBoxes' AS TotalBoxes,EntryDate AS [DOE],EnteredBy AS [USERS]
FROM
(SELECT BoxCode,EntryDate,EnteredBy FROM Patient_vw
WHERE EntryDate > '2013-05-01' AND BoxCode IS NOT NULL
GROUP BY BoxCode,EntryDate,EnteredBy)AS  pw
PIVOT
(
Count(BoxCode)
FOR BoxCode IN ([DOE],[USERS])
) AS Pvt

Thanks

Joe
0
Question by:JOLEEJJ
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 10
• 5

LVL 49

Expert Comment

ID: 39252368
Can you provide a small sample of this data?
SELECT BoxCode, EntryDate, EnteredBy FROM Patient_vw

does the field [EntryDate] have times like 09:17:12? (or is it always 00:00:00?)

what does the expected result look like?
0

LVL 49

Expert Comment

ID: 39252376
one more, sorry,
how any different codes are used in the field [BoxCode]?

let's assume for a moment that the answer to this is 'A','B','C'

are you expecting a result like this?

TotalBoxes EntryDate   EnteredBy  count_of_A   count_of_B   count_of_C
TotalBoxes 2013-05-01 PortletPaul  100              50                 25
0

LVL 49

Expert Comment

ID: 39252743
Perhaps this approach will work for you, it arranges the pivot columns by vales of BoxCode and then shows the count per person, per day. So it loos like this for A B C in BoxCode:
``````TOTALBOXES    ENTEREDBY  ENTRYDATE      A    B    C
TotalBoxes    Grumpy    May, 02 2013    1    1    1
TotalBoxes    Happy     May, 03 2013    1    1    1
TotalBoxes    Sleepy    May, 04 2013    1    1    1
TotalBoxes    Sneezy    May, 01 2013    1    1    1
``````
the code is:
``````DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(BoxCode)
FROM Patient_vw
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT TotalBoxes, EnteredBy, EntryDate , ' + @cols + ' from
(
select
''TotalBoxes'' as TotalBoxes
, EnteredBy
, EntryDate
, BoxCode
from Patient_vw
) AS x
pivot
(
count(BoxCode)
for BoxCode in (' + @cols + ')
) p '

exec (@query)
;
``````
and a simulation may be inspected here: http://sqlfiddle.com/#!3/2d9b6/1
0

Author Comment

ID: 39254795
Hi All,

The sample result from this query :

"SELECT EntryDate,EnteredBy,BoxCode FROM
Patient_vw
WHERE EntryDate > '2013-05-01' "

is as follows:

EntryDate                                EnteredBy    BoxCode
2013-05-01 09:30:01.677      STEPHEN           K0644691
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:31:35.593      MARTIN           K0644608
2013-05-02 09:32:06.233      JAMES           K0644691
2013-05-02 09:32:55.340      KEVIN           K0644691
2013-05-02 09:34:35.430      KEVIN           K0644648
2013-05-03 09:35:21.433      TAYLOR           K0644695

Now, I want the Pivot query should look like this:

Entry Date                               EnteredBy    TotalBoxes
2013-05-01                              Stephen       2
2013-05-01                              Martin         1
2013-05-02                              James          1
2013-05-02                              KEVIN          2
2013-05-03                              TAYLOR        1

Thanks

Joe
0

LVL 49

Expert Comment

ID: 39254993
that's not a pivot, just a group by will do this job, however we do have to deal with the time in the datetime field. thanks for the sample data and expected results - this makes a huge difference. back soon.
0

LVL 49

Expert Comment

ID: 39255010
produced results:
``````ENTRY DATE	ENTEREDBY	TOTALBOXES
05-01-2013	MARTIN		1
05-01-2013	STEPHEN		2
05-02-2013	JAMES		1
05-02-2013	KEVIN		2
05-03-2013	TAYLOR		1

-- produced by this code:
select
convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 110) as [Entry Date]
, EnteredBy
, count(*) as TotalBoxes
from Patient_vw
group by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
order by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
``````
see this working at: http://sqlfiddle.com/#!3/ae204/6
0

LVL 49

Expert Comment

ID: 39255024
some small corrections:

this requirement IS a 'pivot' but does not require use of the 'pivot feature' (sorry bad wording earlier)

for the requested yyyy-mm-dd format, use format mask 120 but need to remove hh:mm:ss, so use left() for this.

the amended query is
``````ENTRY DATE	ENTEREDBY	TOTALBOXES
2013-05-01	MARTIN		1
2013-05-01	STEPHEN		2
2013-05-02	JAMES		1
2013-05-02	KEVIN		2
2013-05-03	TAYLOR		1

-- produced by
select
left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, EnteredBy
, count(*) as TotalBoxes
from Patient_vw
group by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
order by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
``````
see this at: http://sqlfiddle.com/#!3/ae204/8
0

Author Comment

ID: 39255070
Hi

I am sorry it is my mistake, I haven't given the proper data.  I know you can get the result by grouping.  However my data contains duplicate BoxCodes.  The grouping bring the duplicate as another records.

EntryDate                                EnteredBy    BoxCode
2013-05-01 09:30:01.677      STEPHEN           K0644691
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:30:42.080      STEPHEN           K0644692
2013-05-01 09:30:42.080      STEPHEN           K0644693
2013-05-01 09:31:35.593      MARTIN           K0644608
2013-05-02 09:32:06.233      JAMES           K0644691
2013-05-02 09:32:55.340      KEVIN           K0644691
2013-05-02 09:34:35.430      KEVIN           K0644648
2013-05-03 09:35:21.433      TAYLOR           K0644695

in the above example, for stephen it should be 3 total boxes instead of 5 boxes.

Thanks

Joe
0

LVL 49

Expert Comment

ID: 39255113
for a total of 3, not 5, you either exclude some data, or you include BoxCodes into the calculations, like this:
``````ENTRY DATE	ENTEREDBY	BOXCODE	TOTALBOXES
2013-05-01	MARTIN		K0644608	1
2013-05-01	STEPHEN		K0644691	1
2013-05-01	STEPHEN		K0644692	3
2013-05-01	STEPHEN		K0644693	1
2013-05-02	JAMES		K0644691	1
2013-05-02	KEVIN		K0644648	1
2013-05-02	KEVIN		K0644691	1
2013-05-03	TAYLOR		K0644695	1

select
left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, EnteredBy
, BoxCode
, count(*) as TotalBoxes
from Patient_vw
group by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
, BoxCode
order by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
, BoxCode
``````
Please visit http://sqlfiddle.com/#!3/f253f/2, there are 2 queries to consider there. the one you see above AND one using the 'pivot feature'
``````-- and as an example of pivot - which is vastly different

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(BoxCode)
FROM Patient_vw
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = 'SELECT TotalBoxes, EnteredBy, [Entry Date] , ' + @cols + ' from
(
select
''TotalBoxes'' as TotalBoxes
, EnteredBy
, left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, BoxCode
from Patient_vw
) AS x
pivot
(
count(BoxCode)
for BoxCode in (' + @cols + ')
) p '

exec (@query)
;
``````
0

Author Comment

ID: 39255161
Hi

Your result set repeats the EnteredBy more than once.  It should be  a distinct value and also
I have more 100000 boxcodes entereed with distinct value of more than 5000 on various dates by various users.

So, my result set should be how many boxes are entered for distinct dates for distinct users.

Thanks

Joe
0

LVL 49

Expert Comment

ID: 39255197
@JOLEEJJ

thank you, please then explain - in detail - how I arrive at a total of 3 and not 5

>>in the above example, for stephen it should be 3 total boxes instead of 5 boxes.
(your words not mine)

as you can see I have been trying quite hard to meet your every desire, but you are changing the rules each time. As I said before, to arrive at a total of 3 for Stephen on  2013-05-01 we either ignore some records, or we get more rows, or we get a total of 5.

ENTRY DATE      ENTEREDBY      BOXCODE      TOTALBOXES
2013-05-01      STEPHEN            K0644691      1 --<< why is this ignored?
2013-05-01      STEPHEN            K0644692      3
2013-05-01      STEPHEN            K0644693      1 --<< why is this ignored?

you have not explained "something" that is in your mind - but not yet in mine.
0

Author Comment

ID: 39255232
Dear Paul,

I am sorry if I have not made clear.  Anyway,  Let us take the user STEPHEN.
Yes. I agree he has entered 5 boxes.  But what I need is the total count of distinct boxes.
In this case, the distinct boxes are: 1) K064491, 2) K0644692, 3) K0644693
Therefore the total number of boxes that STEPHEN has entered should be only 3 not 5 because box K0644692 has been repeated 3 times.
It may not make sense logically but we need to arrive the distinct no of boxes entered by each user for each day.
I hope I have made it clear this time.
Thanks for your patience.
I really appreciate it.

Joe
0

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39255315
Excellent - thanks. You have provided your own solution.... just use "distinct boxcode"
see line 12 below
``````ENTRY DATE	ENTEREDBY	TOTALBOXES
2013-05-01	MARTIN		1
2013-05-01	STEPHEN		3
2013-05-02	JAMES		1
2013-05-02	KEVIN		2
2013-05-03	TAYLOR		1

-- produced by
select
left(convert(varchar,dateadd(day, datediff(day,0, EntryDate ), 0), 120),10) as [Entry Date]
, EnteredBy
, count(distinct BoxCode) as TotalBoxes
from Patient_vw
group by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
order by
dateadd(day, datediff(day,0, EntryDate ), 0)
, EnteredBy
``````
http://sqlfiddle.com/#!3/f253f/4
0

Author Closing Comment

ID: 39255392
Thanks Paul.  This is what I needed.

Cheers,

Joe
0

LVL 49

Expert Comment

ID: 39255404
v.happy I got there in the end - I'm a bit slow it seems today.

just as a tip, with sample data and expected results I could arrive at the solution - before then I was really guessing (and hoping to attract your attention to my dilemma)...

thanks for the prompt closure, that's always appreciated. Cheers, Paul
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace â€¦
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, â€¦
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vulnâ€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
###### Suggested Courses
Course of the Month8 days, 8 hours left to enroll

#### 596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.