rotate a table

Question

Post reply

rotate a table

hren

Ten Centuries

Points: 1220
More actions
July 3, 2006 at 2:27 pm

#118535

I have a select statement like:
SELECT Company, FirstName, LastName, Email FROM Table and get recordset like:
Company 1 John Doe my@mail.com
Company 2 Steve Smith steve@smith.com
Company 3 Laura Lenon laura@lenon.com
How can I modify this to get result like:
--- Company 1 Company 2 Company 3
FirstName John Steve Laura
LastName Doe Smith Lenon
Email my@mail.com steve@smith.com laura@lenon.com
Please help

Viewing 8 posts - 1 through 7 (of 7 total)

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

hren Ten Centuries Points: 1220 More actions · Answer 1

oh, forgot to mention.

select statement is dynamic, ea

select ' + @fields + ' from table

so we don't really know what @fields may have.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

What datatype you plan to use for columns in rotated table?

_____________
Code for TallyGenerator

stax68 SSChampion Points: 11711 More actions · Answer 3

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=291926

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

hren Ten Centuries Points: 1220 More actions · Answer 4

Unfortunately I do not use SQL server 2005.

Further more, above link is talking about static 90 degree turn, as he knows all the columns, I do not. It will be passed into procedure and vary at all the time.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

This looks like a dynamic pivot table request, and I suggest you refer these articles:

http://www.sqlteam.com/item.asp?ItemID=2955

http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

stax68 SSChampion Points: 11711 More actions · Answer 6

>he knows all the columns, I do not. It will be passed into procedure and vary at all the time.

Eric knows the columns in the 'before' recordset, but not the columns in the query output. If you mean you don't know the column names in the output, as you would need to to use CASE() or PIVOT, there's no problem. That's what the code in the quoted post is designed to deal with, hence the dynamic SQL.

If you mean you want code which can rotate any table without aggregation, remember not to do it on a huge table!

For this, you'd need to get the column names and datatypes from syscolumns or information_schema, then (probably) use nested dynamic SQL. I generally try to avoid typing 12 single-quotes in a row, but have fun!

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

stax68 SSChampion Points: 11711 More actions · Answer 7

I've had a go at this, trying to keep the dynamic SQL to a minimum. The processing could possbly be made more efficient, but the task itself limits the options there.

The main outstanding problem is when the row size exceeds the maximum. The code as it stands fills the output table from left to right, ordering by the specified unique key column. When a row exceeds the available space on the page, it raises an error and stops filling the table. You still see the results though. I've started adding some code to cleanly stop processing before the row fills up, but I'm hampered by using SQL v9 for development, which allows more than 8060 characters in a row, and also has some other changes which I'm still looking into.

At present, I've added some code which attempts to keep track of row size - the output is printed to the messages window. It would also be preferable to add columns only as needed - this could possibly be done by combining the two cursors into one nested cursor. At present, the code ignores columns defined as >200 chars, as well as large datatypes and XML. THis whole area could be cleaned up quite a bit. I've had enough for the moment, so I'm posting the code as is, in the hope that it may be useful as a starting point.

It would be possible to get rid of the cursors, too, but there may not be much advantage. Basically, I've concentrated on the functionality and left the optimisation (possiby a complete rewrite) for later (and for someone else!).

set

nocount on

go

--for v9

--exec sp_dbcmptlevel Scheduler, 80

go--

drop

table #TESTbefore

go--

create

table #TESTbefore([PK'] int identity primary key clustered, _Name varchar(30),BirthDate smalldatetime, Car varchar(20), number int

,

[some @#//?. horribly

named " column '] as (cast(substring(replicate(isnull(_Name,'<NULL>') + N' ' + isnull(Car,'<NULL>') + N' ',5),1,50) as varchar(50))))

go--

insert

#TESTbefore(_Name, BirthDate, Car, number)

select

'Bob', '3/4/1985', 'Nissan', 1 union all

select

'Jim', '5/4/1943', 'Daewoo', 2 union all

select

'Sid', '1/1/2000', 'Smart', 3 union all

select

'Nancy', '8/19/1989', null, 4 union all

select

'Johnny', null, 'Triumph', 5 union all

select

'Sue', '8/19/1929', 'Bike', 6 union all

select

'Bertram', '8/19/2009', 'Sinclair C5', 7 union all

select

'Frampton', '8/19/1972', 'Fiat', 8 union all

select

'Felicity', '8/19/1984', null, 9 union all

select

'Horace', '8/19/1947', 'Citroen', 10

insert

#TESTbefore(_Name, BirthDate, Car, number)

select

_name + 'x', BirthDate+1, Car, number+1 from #TESTbefore --*2

insert

#TESTbefore(_Name, BirthDate, Car, number)

select

_name + 'y', BirthDate+2, Car, number+2 from #TESTbefore --*4

insert

#TESTbefore(_Name, BirthDate, Car, number)

select

_name + 'z', BirthDate+4, Car, number+4 from #TESTbefore --*8

insert

#TESTbefore(_Name, BirthDate, Car, number)

select

_name + 'a', BirthDate+8, Car, number+8 from #TESTbefore --*16

insert

#TESTbefore(_Name, BirthDate, Car, number)

select

_name + 'b', BirthDate+16, Car, number+16 from #TESTbefore --*32

-----------------------------------------------------------------------------------------

go--

create

function dbo.quotestring(@string varchar(8000))

returns

varchar(8000)

as

begin

return replace(@string, '''', '''''')

end

go--

set

nocount on

go--

--select * from #TESTbefore

declare

@tabname sysname, @PKcolname sysname

--

select

@tabname = '[#TESTbefore]', @PKcolname = 'PK'''

--

declare

@objid int, @sql nvarchar(4000), @prmlist nvarchar(4000)

, @istemptab bit, @keyval varchar(200), @colname sysname, @datalength int, @outputrowlen int

,

@inputkeycount int, @emptyrowsize int

declare @inputcols table(id int identity unique, name sysname unique, outputrowlen int not null default 0)

declare

@inputkeys table(id int identity unique, keyval varchar(200) unique)

create

table #output(column_name sysname primary key clustered)

select

@tabname = case when @tabname like '[[]%]' then substring(@tabname,2,len(@tabname)-2) else @tabname end

,

@PKcolname = case when @PKcolname like '[[]%]' then substring(@PKcolname,2,len(@PKcolname)-2) else @PKcolname end

select @istemptab = case when left(@tabname,1) = N'#' then 1 else 0 end

,

@prmlist = '@objid int output'

select

@sql = case when @istemptab = 1 then N'use tempdb ' else N'' end

+

'select @objid = object_id(N''' + dbo.quotestring(@tabname) + ''')'

exec sp_executesql @sql, @prmlist, @objid output

select @sql = N'select top 1023 cast(' + quotename(@PKcolname) + N' as varchar(200)) from ' + @tabname

+

N' order by ' + quotename(@PKcolname)

insert

@inputkeys(keyval)

exec

sp_executesql @sql

select

@inputkeycount = count(*) from @inputkeys

select

@emptyrowsize = 6 + ((@inputkeycount + 7) / 8) + (@inputkeycount*2)

insert

@inputcols(name,outputrowlen)

select

name,@emptyrowsize from tempdb.dbo.syscolumns where @istemptab = 1 and id = @objid and xtype not in (34,35,98,99,241) and length <= 200 and name <> @PKcolname order by name

insert @inputcols(name,outputrowlen)

select

name,@emptyrowsize from dbo.syscolumns where @istemptab = 0 and id = @objid and xtype not in (34,35,98,99,241) and length <= 200 order by name

declare cr_rowstocols cursor local fast_forward for

select keyval from @inputkeys order by id

open

cr_rowstocols

fetch

cr_rowstocols into @keyval

while

@@fetch_status = 0

begin

select @sql = N'alter table #output add ' + quotename(@PKcolname + N'_' + dbo.quotestring(@keyval)) + N' varchar(200) null'

print @sql

exec sp_executesql @sql

fetch cr_rowstocols into @keyval

end

close

cr_rowstocols

deallocate

cr_rowstocols

--for v9

--exec sp_tableoption N'#output', 'text in row', 'ON'

--

insert

#output(column_name) select name from @inputcols

declare

cr_onecellupdates cursor local forward_only keyset for

select k.keyval, c.name, c.outputrowlen from @inputkeys k cross join @inputcols c order by k.id

for

update of c.outputrowlen

open

cr_onecellupdates

fetch

cr_onecellupdates into @keyval, @colname, @outputrowlen

while

@@fetch_status = 0

begin

select @datalength = 0

, @sql = N'update o set ' + quotename(@PKcolname + N'_' + @keyval) + N' = t.' + quotename(@colname)

+ N', @datalength = isnull(datalength(t.' + quotename(@colname) + N'),0) + 2 from #output o, ' + quotename(@tabname) + N' t

where o.column_name = N''' + dbo.quotestring(@colname) + N'''

and t.' + quotename(@PKcolname) + N' = ''' + dbo.quotestring(@keyval) + N''''

, @prmlist = N'@datalength int output'

--print @sql

exec sp_executesql @sql, @prmlist, @datalength output

--for testing

if @colname like 'some%'

print 'PK:' + str(cast(@keyval as int))

+ 'PrevRowlen:' + str(@outputrowlen) + 'ValLen:'

+ str(@datalength) + 'NewRowLen:' + str(@outputrowlen + @datalength)

update @inputcols set outputrowlen = @outputrowlen + @datalength where current of cr_onecellupdates

fetch cr_onecellupdates into @keyval, @colname, @outputrowlen

end

close

cr_onecellupdates

deallocate

cr_onecellupdates

go

select

* from #output

go

drop

table #output

go

drop

function dbo.quotestring

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant