SQL Server Pivot tables

Question

SQL Server Pivot tables

SJanki

SSCrazy

Points: 2847
More actions
December 27, 2012 at 1:18 pm

#266642

I have a table that keeps track of a store open, closed, reopen, closed (again) , reopen(again) etc dates.
The table has StoreId , AuditTypeId (Open, Close, Reopen) and a date.
I want a report/TSQL that will give me a result like-->
StoreId, Open Date, Closed Date , ReOpen Date, Closed Date 2, ReOpenDate 2 .......
There can be no Reopen & Re-Close for a store or the store could have been closed twice and reopened twice etc.
Could you please give me an idea how to write the query to get this result.
Thanks!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SJanki (12/27/2012)
I have a table that keeps track of a store open, closed, reopen, closed (again) , reopen(again) etc dates.
The table has StoreId , AuditTypeId (Open, Close, Reopen) and a date.
I want a report/TSQL that will give me a result like-->
StoreId, Open Date, Closed Date , ReOpen Date, Closed Date 2, ReOpenDate 2 .......
There can be no Reopen & Re-Close for a store or the store could have been closed twice and reopened twice etc.
Could you please give me an idea how to write the query to get this result.
Thanks!!

This is not as simple as it seems. It sounds like you need a dynamic solution where the number of columns is unknown. Please refer to the 2 articles in my signature about cross tabs. The seconds one about dynamic cross tabs is exactly what you need for this sort of thing.

Of course if this is a report, doing it in the front end is likely to perform faster than doing this in sql.

_______________________________________________________________

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eklavu Right there with Babe Points: 790 More actions · Answer 2

Looks like a challenging solutions and I want to try it. I'm not busy today because my boss is on holiday (yehey!!!) :-).Can you post some data and sample output so I can work something for today.

SJanki SSCrazy Points: 2847 More actions · Answer 3

eklavu (12/27/2012)
Looks like a challenging solutions and I want to try it. I'm not busy today because my boss is on holiday (yehey!!!) :-).Can you post some data and sample output so I can work something for today.

Hi,

Please find the example data-->

Master Table-->Store_Audit_Type

Audit_Type_Id Audit_Type_Description

1 New

2 Closed

3 ReOpen

Data in the table

StoreId Audit_Type_Id CreatedDate

1 1 1/1/2012

1 2 4/1/2012

1 3 6/1/2012

1 2 11/1/1012

2 1 1/5/2012

2 2 5/10/2012

3 1 12/1/2012

in the above example, Store 1 was newly opened on 1/1/2012 , then was closed on 4/1/2012.

It was Reopened on 6/1/2012 and again closed on 11/1/2012

Store 2 was newly opened on 1/5/2012, then closed on 5/10/2012

Store 3 was opened on 12/1/2012 ( this one is not yet closed )

Hope this will help to understand the scenario.

Thank you!!

Good luck!

Eklavu Right there with Babe Points: 790 More actions · Answer 4

I still don't understand some of the requirements.

StoreId, Open Date, Closed Date , ReOpen Date, Closed Date 2, ReOpenDate 2 .......

There can be no Reopen & Re-Close for a store or the store could have been closed twice and reopened twice etc.

you said There can be no Reopen. Why is it that there is a ReOpen Date field in your report?

vinu512 SSCoach Points: 15729 More actions · Answer 5

Is this what you are looking for?

--Creating Tables

Create Table Store_Audit_Type

(

Audit_Type_Id Int,

Audit_Type_Description NVarchar(30)

)

Create Table Store_Audit_Details

(

StoreId Int,

Audit_Type_Id Int,

CreatedDate Date

)

--Inserting Sample Data

Insert Into Store_Audit_Type

Select 1, 'New'

Union ALL

Select 2, 'Closed'

Union ALL

Select 3, 'ReOpen'

Insert Into Store_Audit_Details

Select 1, 1, '2012/01/01'

Union ALL

Select 1, 2, '2012/04/01'

Union ALL

Select 1, 3, '2012/06/01'

Union ALL

Select 1, 2, '2012/08/01'

Union ALL

Select 1, 3, '2012/10/01'

Union ALL

Select 1, 2, '2012/11/01'

Union ALL

Select 2, 1, '2012/01/05'

Union ALL

Select 2, 2, '2012/05/10'

Union ALL

Select 3, 1, '2012/12/01'

--Dynamic Pivot

Declare @sql NVarchar(MAX), @cols NVarchar(MAX)

Select @cols = STUFF((Select DISTINCT ',MAX(Case When rn = ''' + rn + ''' Then CreatedDate Else NULL End) As ' + rn

From (Select DISTINCT Audit_Type_Description + Cast(ROW_NUMBER() Over (Partition By StoreId, Audit_Type_Description Order By StoreId) As Varchar) As rn

From Store_Audit_Details As a

JOIN Store_Audit_Type As b ON a.Audit_Type_Id = b.Audit_Type_Id ) As q FOR XML PATH('')),1,1,'')

Set @sql = 'Select StoreId, ' + @cols

+ ' From

(

Select StoreId, Audit_Type_Description, CreatedDate, Audit_Type_Description + Cast(ROW_NUMBER() Over (Partition By StoreId, Audit_Type_Description Order By StoreId) As Varchar) As rn

From Store_Audit_Details As a

JOIN Store_Audit_Type As b ON a.Audit_Type_Id = b.Audit_Type_Id

) As p

Group By StoreId'

Execute (@sql)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

SJanki SSCrazy Points: 2847 More actions · Answer 6

vinu512 (12/28/2012)
Is this what you are looking for?
[/code]

Thank you Venu512, This was the quick help I really needed to produce the year end report!

SJanki SSCrazy Points: 2847 More actions · Answer 7

This is not as simple as it seems. It sounds like you need a dynamic solution where the number of columns is unknown. Please refer to the 2 articles in my signature about cross tabs. The seconds one about dynamic cross tabs is exactly what you need for this sort of thing.
Of course if this is a report, doing it in the front end is likely to perform faster than doing this in sql.

Thank you Seane. Really useful articles.

vinu512 SSCoach Points: 15729 More actions · Answer 8

SJanki (12/28/2012)
vinu512 (12/28/2012)
Is this what you are looking for?
[/code]
Thank you Venu512, This was the quick help I really needed to produce the year end report!

You're Welcome....By the way the name's Vinu not Venu. 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉