return data as column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

return data as column name

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-31 : 15:09:53

Hi, there,

I need some body to help me on a tsql that will retrieve data value as the column names.

IF NOT OBJECT_ID ('temp..#tblHist') is null drop table #tblHist

Create table #tblHist
(txtSKU Varchar(10), dteDate DateTime, SalesWeek Decimal(4,4))

Insert into #tblHist Values ('980680','2006-12-31', .4)
Insert into #tblHist Values ('980680','2007-01-07', .2)

--Following is what I have built to pivot the data.
Declare @LatestSunday as datetime
SET @LatestSunday = dateadd(dd,(datediff(dd,-53684, getdate())/7)*7,-53684)
SELECT txtSKU,
MAX(CASE WHEN DATEDIFF (ww, dteDate, @LatestSunday) =52 THEN SalesWeek END) AS 'Wk1',
MAX(CASE WHEN DATEDIFF (ww, dteDate, @LatestSunday) =51 THEN SalesWeek END) AS 'Wk2'
FROM #tblHist
GROUP BY txtSKU

--I got:
txtSKU/Wk1/Wk2
980680/.4000/.2000

--Now I want to have the data like this:
txtSKU/2006-13-31/2007-01-07
980680/.4000/.2000

--In another word, replace the wk1, wk2 with dynamic data from the dteDate filed.

--note: dteDate is always a sunday, use @LatestSunday to find last sunday, then compare to dteDate and Pivot the data

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 15:22:27

Why don't you handle this at the application layer (where it should be done)?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-31 : 15:46:04

Tara,

Thank you for your reply!

That certainly is a valid approach. If I could not find the t-sql way, I might have to try that.

However, our thin client is way behind the db part of the app in term of coding power, so normally they ask me to solve it on the db side.

I remember seeing a block of dynamic sql somewhere that will do the trick.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 16:18:00

Yes dynamic SQL would work but are you really willing to sacrifice performance and security for something that shouldn't be done at the database layer?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-12-31 : 16:51:57

Yah, that is kind of the sad reality.
Dealing the issue in the sql server has the advantages of 1)returning less data to the client, 2) use server power to process business logic instead of browser script, and 3)set base instead of looping line by line.

The performance is a debatable issue. Either get hit on the sever or at the client side. To the users, there is not much different.

sql injection and other security concerns could minimize by wrapping the d-sql in sp...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 17:01:04

I don't see how you would return less data here nor do I see why you'd have to loop instead of a set-based approach for this problem.

You can't minimize the security risk as it requires direct table access instead of just exec on stored procedures.

And yes the performance issue could be noticeable to the clients.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-02 : 16:30:07

You can get the dates as the column names if you know the date values before hand using a pivot operator like so:

IF NOT OBJECT_ID ('temp..#tblHist') is not null drop table #tblHist

Create table #tblHist
(txtSKU Varchar(10), dteDate DateTime, SalesWeek Decimal(4,4))

Insert into #tblHist Values ('980680','2006-12-31', .4)
Insert into #tblHist Values ('980680','2007-01-07', .2)

--Following is what I have built to pivot the data.
Declare @LatestSunday as datetime
SET @LatestSunday = dateadd(dd,(datediff(dd,-53684, getdate())/7)*7,-53684)

SELECT * FROM
(
SELECT txtSKU, dteDate,
MAX(CASE WHEN DATEDIFF (ww, dteDate, @LatestSunday) =52
THEN SalesWeek END) AS 'Wk'
FROM #tblHist
GROUP BY txtSKU, dteDate

UNION ALL

SELECT txtSKU, dteDate,
MAX(CASE WHEN DATEDIFF (ww, dteDate, @LatestSunday) =51
THEN SalesWeek END) AS 'Wk'
FROM #tblHist
GROUP BY txtSKU, dteDate
) a
PIVOT ( SUM( a.Wk ) FOR a.dteDate IN ( [2006-12-31], [2007-01-07] )) p

If you dont know the date values then you would most likely have to write it dynamically... unless someone else has a better suggestion.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-01-04 : 10:25:13

Thanks, JDAMAN!

The date values is known before hand as it were stored in the table.

However, when I replaced your last line with following, it failed.

PIVOT ( SUM( a.Wk ) FOR a.dteDate IN ( Select dteDate from #tblHist)) p

Also, I need a solution for sql2000 too, where there is no PIVOT.

I will try to crank that part out if my 05 is working.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-10 : 12:53:46

Hommer,

Here is your dynamic script (although Tara makes valid points against doing things this way):

DECLARE @sql varchar(MAX), @columnlist varchar(1000)

SELECT @columnlist = COALESCE(@columnlist+', ['+CONVERT(VARCHAR, dteDate, 112)+']', '['+CONVERT(VARCHAR, dteDate, 112)+']', @columnlist) FROM #tblHist

SET @sql = '
SELECT * FROM
(
SELECT txtSKU, CONVERT(VARCHAR, dteDate, 112) AS dteDate,
MAX(CASE WHEN DATEDIFF (ww, dteDate, CAST('''+CONVERT(VARCHAR, @LatestSunday, 112)+''' AS DATETIME)) =52
THEN SalesWeek END) AS ''Wk''
FROM #tblHist
GROUP BY txtSKU, CONVERT(VARCHAR, dteDate, 112)

UNION ALL

SELECT txtSKU, CONVERT(VARCHAR, dteDate, 112) AS dteDate,
MAX(CASE WHEN DATEDIFF (ww, dteDate, CAST('''+CONVERT(VARCHAR, @LatestSunday, 112)+''' AS DATETIME)) =51
THEN SalesWeek END) AS ''Wk''
FROM #tblHist
GROUP BY txtSKU, CONVERT(VARCHAR, dteDate, 112)
) a
PIVOT ( SUM( a.Wk ) FOR a.dteDate IN ( '+ @columnlist +' )) p'

EXEC (@sql)

Also you should study the pro's and con's of dynamic sql: http://www.sommarskog.se/dynamic_sql.html

Hommer
Aged Yak Warrior

808 Posts

Posted - 2008-01-11 : 13:25:44

Thank you all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources