Write Query Dynamic Generated Column

Question

Post reply

Write Query Dynamic Generated Column

shatrughna

Ten Centuries

Points: 1037
More actions
July 6, 2010 at 12:45 am

#226424

Hi All,
I want to write a query on dynamic generated column.
My table structure as below with some sample data:
IDDispositionCDATE
1Busy 7/1/2010
2Ringing7/1/2010
3Callback7/1/2010
4Callback7/1/2010
5Busy 7/2/2010
My output should come like this:
Disposition201007012010070220100703
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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Maddy...! SSC Eights! Points: 995 More actions · Answer 1

[font="Arial"]

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

[/font]

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 SSC Guru Points: 107049 More actions · Answer 2

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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Alban Lijo SSC Veteran Points: 282 More actions · Answer 3

-- 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 'Busy', '7/2/2010'

union all Select 'Callback','7/3/2010'

union all Select 'Busy', '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

[font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]

LutzM SSC Guru Points: 107049 More actions · Answer 4

@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[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Alban Lijo SSC Veteran Points: 282 More actions · Answer 5

@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. 🙂

[font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]