Row to Columns - 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 2008 Forums

Transact-SQL (2008)

Row to Columns

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-23 : 08:55:43

hi

I have found this example in the SQLTeam Forum.

declare @t table(num int)
insert into @t
select 101 union all
select 102 union all
select 103
select num from @t
declare @s varchar(8000)
select @s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + ' as tranID'
from @t
exec('select '+@s)

How should i reflect the column name as 101, 102, 103 and so on instead of tranID? Thanks a lot

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-23 : 09:16:57

[code]select @s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + ' as [' + cast(num as varchar(10)) + ']'
[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 09:21:26

well the answer to your question is this:


declare @t table(num int)
insert into @t 
select 101 union all 
select 102 union all 
select 103 
select num from @t
declare @s varchar(8000)
select @s = COALESCE(@s + ',', '') + cast(num as varchar(10)) + ' as ' + QUOTENAME(CAST([num] AS VARCHAR(10)))
from @t
PRINT @s
exec('select '+@s)

But that code really doesn't look nice to me.

What are you actually trying to do? There will be better ways.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-23 : 10:09:00

Thanks guys. I am trying to map out how the results set should be. The above is the first step. Thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 10:16:40

quote:
Originally posted by sg2255551

Thanks guys. I am trying to map out how the results set should be. The above is the first step. Thanks a lot

still didnt understand why you want to alias it as same as value list

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-23 : 10:31:33

ok

i have a table a single columns with dates
2012-02-01
2012-02-02
2012-02-03
2012-02-04
2012-02-05
2012-02-06
2012-02-07
2012-02-08
2012-02-09
2012-02-10
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15
2012-02-16
2012-02-17
2012-02-18

and another table as below

declare @t table(num int, Prod varchar(20),ORDate datetime)
insert into @t
select 101,'A','2012-02-02 00:00:00.000' union all
select 102,'B','2012-02-07 00:00:00.000' union all
select 103,'C','2012-02-16 00:00:00.000'
select num from @t
declare @s varchar(8000)
select @s = COALESCE(@s + ',', '') + cast(num as varchar(10))+','+ cast(Prod as varchar(10)) + ' as ' + QUOTENAME(CAST([strg] AS VARCHAR(10)))
from @t
PRINT @s
exec('select '+@s)

I hope to have the results set to be like

Date 101 102 103
2012-02-01
2012-02-02 A
2012-02-03
2012-02-04
2012-02-05
2012-02-06
2012-02-07 B
2012-02-08
2012-02-09
2012-02-10
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15
2012-02-16 C
2012-02-17
2012-02-18

How should i go about it? Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 10:44:58

there are 2 ways.

1) Make the database produce a simple list of all the dates joined to the values


SELECT
    d.[date] AS [Date]
    , t.[num] AS [Num]
    , t.[Prod] AS [Prod]
FROM
    <DateTable> AS d
    LEFT JOIN @t AS t ON t.[ORDate] = d.[Date]
ORDER BY
    d.[Date]
    , t.[Num]

And then pivot the results easily in your calling application (because everything is in order it will be easy to write a csv file or produce the repport

2) A Dynamic Cross tab.
Start reading here:
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

I'd advice (1) because the database isn't great at the pivoting and (2) is a hack. Others will tell you different but they'll admit in their heart of hearts that its true

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-23 : 11:02:41

Thanks a lot. I will try that tomorrow. I am tired now and wish to get some sleep. Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 14:53:47

see this too

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-24 : 00:25:02

hi

I am stuck here. I am able to get the result in table but unable to pivot it. I am binding to a gridview. Thanks a lot

The result set should be

Date CountryA CountryB CountryC .... CountryZ
2012-02-01
2012-02-02
2012-02-03 VINET
2012-02-04
2012-02-05
2012-02-06 WELLI
2012-02-07
2012-02-08
2012-02-09
2012-02-10 FOLKO
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15
2012-02-16
2012-02-17
2012-02-18

Select * from (
SELECT
d.StoreDate AS [Date]
, t.ShipCountry AS [ShipCountry]
, t.CustomerID AS [CustID]
FROM dbo.StoredDate AS d
LEFT JOIN dbo.dbo.Orders AS t ON cast(t.OrderDate as date) = Cast(d.StoreDate as date)
where year(d.StoreDate) = 2012 and month(d.StoreDate) = 2
)
pivot
(
Min([CustID]) for pivot_col in ([1996],[1997]) --Replace it ShipCountry
) P

I

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2012-02-26 : 19:59:33

hi

I have found the solution. Now is it possible to turn the dynamic sql to a CTE? Thks a lot

Create Proc test
as
DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT @years = STUFF((SELECT DISTINCT
'],[' + ltrim(Dyear)
FROM Product2
ORDER BY '],[' + ltrim(Dyear)
FOR XML PATH('')
), 1, 2, '') + ']'

SET @query =
'SELECT * FROM
(
SELECT prodId,Dyear,amount
FROM Product2
)t
PIVOT (SUM(amount) FOR Dyear
IN ('+@years+')) AS pvt'

EXECUTE (@query)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources