Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Write Query Dynamic Generated Column


Write Query Dynamic Generated Column

Author
Message
shatrughna
shatrughna
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 1587
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
Maddy...!
Maddy...!
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 213

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
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7021 Visits: 13559
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
Alban Lijo
Alban Lijo
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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> :-)
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7021 Visits: 13559
@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
Alban Lijo
Alban Lijo
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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> :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search