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

Write Query Dynamic Generated Column Expand / Collapse
Author
Message
Posted Tuesday, July 6, 2010 12:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 3, 2012 12:17 AM
Points: 97, Visits: 1,586
Hi All,
I want to write a query on dynamic generated column.
My table structure as below with some sample data:
ID Disposition CDATE
1 Busy 7/1/2010
2 Ringing 7/1/2010
3 Callback 7/1/2010
4 Callback 7/1/2010
5 Busy 7/2/2010

My output should come like this:
Disposition 20100701 20100702 20100703
Busy 10 5 4
Ringing 2 3 1
Callback 1 3 4

In Output first row CDATE will be come as date dynamically.
Can any one suggest me a good approach to tackle this problem?

Thanks!
Shatrughna






Shatrughna
Post #947728
Posted Tuesday, July 6, 2010 1:08 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 12:33 PM
Points: 45, Visits: 211

I suggest you go thru PIVOT Tables once if you have time...


IF the dates are constant you can frame up this way

select * from (
select ID,Description,
CONVERT(nvarchar(4),year(cdate))+right('00'+CONVERT(nvarchar(2),month(cdate)),2)+
right('00'+CONVERT(nvarchar(2),day(cdate)),2) as Cdate
from dbo.shatTest_tbl
) dataTable
PIVOT
(
COUNT(ID)
for cdate in ([20100701],[20100702])
) PivotTbl
Post #947736
Posted Tuesday, July 6, 2010 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 6,829, Visits: 13,290
Instead of using the PIVOT approach I'd recommend you read the CrossTab article referenced in my signature.
The result of the CrossTab method is similar to the PIVOT approach with one important difference: Once you've understood the concept you can move to the next level: DynamicCrossTab (also linked in my signature). This will allow you to have both, a flexible number of columns and/or variable dates to deal with.

An even better approach would be to leave such data formatting to the application layer (e.g. SSRS)




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #948212
Posted Wednesday, July 7, 2010 11:10 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:14 AM
Points: 30, Visits: 64
-- Create Tables and Insert Values

Create Table tmpDis (ID int Identity(1,1), Disposition VARCHAR(50),CDATE Datetime )
insert into tmpDis (Disposition,CDATE)
Select 'Busy','7/1/2010'
union all Select 'Ringing','7/1/2010'
union all Select 'Callback','7/1/2010'
union all Select 'Callback','7/1/2010'
union all Select 'Busy', '7/2/2010'
union all Select 'Callback','7/3/2010'
union all Select 'Callback','7/3/2010'
union all Select 'Callback','7/3/2010'
union all Select 'Busy', '7/4/2010'
union all Select 'Busy', '7/4/2010'
union all Select 'Busy', '7/4/2010'
union all Select 'Busy', '7/4/2010'
union all Select 'Busy', '7/4/2010'
union all Select 'Ringing','7/4/2010'
union all Select 'Ringing','7/4/2010'
union all Select 'Ringing','7/4/2010'
union all Select 'Ringing','7/4/2010'
union all Select 'Ringing','7/4/2010'

-- Create a procedure

Create PROCEDURE exec uspTmpDynamicTableTry
as
Begin

CREATE TABLE MasterTbl -- for columns and rows
(
RowText VARCHAR(50),
ColumnText VARCHAR(20)
)
INSERT INTO MasterTbl
(
RowText,ColumnText
)
SELECT Distinct tmpDis.Disposition ,convert(varchar, tmpDis.CDATE, 112)

FROM tmpDis
CREATE UNIQUE INDEX IX_MasterRef ON MasterTbl (RowText,ColumnText)

CREATE TABLE ColumnsTbl --For column Header
(
ColumnIndex INT IDENTITY (0, 1),
ColumnText VARCHAR(20)
)

INSERT INTO ColumnsTbl
(
ColumnText
)
SELECT DISTINCT convert(varchar, tmpDis.CDATE, 112)
FROM tmpDis
ORDER BY convert(varchar, tmpDis.CDATE, 112)

CREATE UNIQUE INDEX IX_Columns ON ColumnsTbl (ColumnIndex, ColumnText)

CREATE TABLE RowsTbl -- Output Table
(
Disposition VARCHAR(50)
)
INSERT INTO RowsTbl
(
Disposition
)
SELECT Distinct tmpDis.Disposition
FROM tmpDis

CREATE UNIQUE INDEX IX_Rows ON RowsTbl (Disposition)

DECLARE @ColumnIndex INT,
@MaxColumnIndex INT,
@ColumnText VARCHAR(50),
@SQLstr VARCHAR(1000)

SELECT @ColumnIndex = 0, @MaxColumnIndex = MAX(ColumnIndex) FROM ColumnsTbl


WHILE @ColumnIndex <= @MaxColumnIndex
BEGIN
SELECT @ColumnText = ColumnText
FROM ColumnsTbl
WHERE ColumnIndex = @ColumnIndex

SELECT @SQLstr = 'ALTER TABLE RowsTbl ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(100) NULL DEFAULT ''''' -- Add Rows from ColumnsTbl
EXEC (@SQLstr)
SELECT @SQLstr = 'UPDATE RowsTbl SET ' + QUOTENAME(@ColumnText) + ' = (Select Count(*) from tmpDis A Where convert(Decimal(8),convert(varchar, A.CDATE, 112)) = convert(Decimal(8),ColumnsTbl.ColumnText) and A.Disposition = MasterTbl.RowText Group by A.Disposition ) FROM MasterTbl , ColumnsTbl WHERE ColumnsTbl.ColumnText = MasterTbl.ColumnText and Disposition = MasterTbl.RowText AND ColumnsTbl.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))

EXEC (@SQLstr)

SELECT @ColumnIndex = @ColumnIndex + 1
END

DROP TABLE ColumnsTbl
DROP TABLE MasterTbl

SELECT RowsTbl.* FROM RowsTbl
DROP TABLE RowsTbl
End

------------------------------
Exec uspTmpDynamicTableTry
------------------------------

Output
-------

Disposition     20100701    20100702  20100703  20100704  
----------------------------------------------------------
Busy 1 1 NULL 5
Callback 2 NULL 3 NULL
Ringing 1 NULL NULL 5

(3 row(s) affected)



I hope this Helps



-- Alban Lijo <SQL Rookie>
Post #949001
Posted Thursday, July 8, 2010 9:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 6,829, Visits: 13,290
@Alban Lijo
You might want to have a look at the articles I pointed at in my previous post.
I guess the approach described in those articles will be "slightly faster" than your current solution...




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #949400
Posted Thursday, July 8, 2010 11:34 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:14 AM
Points: 30, Visits: 64
@lmu92
I agree with you, that approach is faster.. but I feel the approach I used is bit easy to understand.
I personally feel that one should understand the solution and approach than finding the solution.
That is the reason I replied with that code.


-- Alban Lijo <SQL Rookie>
Post #949734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse