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/