Write Query Dynamic Generated Column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Write Query Dynamic Generated Column

Write Query Dynamic Generated Column

Rate Topic

Display Mode

Topic Options

Author

Message

shatrughna

Posted Tuesday, July 06, 2010 12:45 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 03, 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

Maddy...!

Posted Tuesday, July 06, 2010 1:08 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 04, 2012 4:33 AM
Points: 45, Visits: 210

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

LutzM

Posted Tuesday, July 06, 2010 3:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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) Wink

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

Alban Lijo

Posted Wednesday, July 07, 2010 11:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 23, 2012 9:25 AM
Points: 28, Visits: 40

-- 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> Smile

Post #949001

LutzM

Posted Thursday, July 08, 2010 9:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

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

Post #949400

Alban Lijo

Posted Thursday, July 08, 2010 11:34 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 23, 2012 9:25 AM
Points: 28, Visits: 40

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

-- Alban Lijo <SQL Rookie> Smile

Post #949734

« Prev Topic | Next Topic »

Permissions