Pivot Table, Row to Column???

Question

Pivot Table, Row to Column???

Borik

Ten Centuries

Points: 1048
More actions
February 3, 2005 at 11:00 am

#65346

Basicly i have a table looks something like this
First Table
id key value
1 a x
1 b y
1 c z
2 a xx
2 b yy
2 c zz
and made a process to make it look like this...
Pivot Table
id a b c
1 x y z
2 xx yy zz
So my question is should i worry about the performance of Pivot Table as there become more and more Key/Value pairs... also i want to know if there also benefit to what i am doing in your opinion?!
Table get populated and in real time i also want populate the pivot table in real time... and do all the Reporting on the Pivot table Vs the First Table...
Basicly let me know what you think?!

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I, personally, like the pivot table as it is easier to look at and understand. What would concern me is, are you having to ALTER TABLE to add more key's? Is the Pivot Table indexed? Is this affecting table and record-locking when you handle this in 'real time'?

Although reporting is important, the database design should not be constructed to achieve reporting ease. Been there, and it gets ugly....

I wasn't born stupid - I had to study.

Borik Ten Centuries Points: 1048 More actions · Answer 2

Pivot Table is Index on id witch is PK with Clustering...

I might add more index later...

Well Becouse this table is Write only Once, No Modifications, i can do Selects with(NoLock)...

Further, my Colunm with are Dynamic they growth when they needs to, becouse SQL Row is Limited to 8000Bites and i have alot of keys i need to do this...

What whould be optimal plan to achive Good DB desigh and Ease of Reporting???

Thank You for your Help

rhunt SSCertifiable Points: 5868 More actions · Answer 3

Quick question - do your x's, y's, xx's, and yy's represent numbers? I'm assuming they do based on your pivot's structure.

If by chance you litteraly are referring to character data, how did you get them all on the same row?

Sum can't be used with the statement below if VALUE is a not a number.

SELECT ID,

(CASE WHEN = 'A' THEN VALUE ELSE NULL END) AS a,

(CASE WHEN = 'B' THEN VALUE ELSE NULL END) AS b,

(CASE WHEN = 'C' THEN VALUE ELSE NULL END) AS c

FROM tableName

Thanks

Ryan

Borik Ten Centuries Points: 1048 More actions · Answer 4

Colunm are a,b,c, etc... i don't know how many Keys there are, they might change over time...

Values are x,y,z,xx,yy,zz... etc there are strings... becouse of keys varieaty...

i do not need sum i guess but if i did SQL converts string representation of number to number on the fly...

further more i used a cursor to loop trough the table and build it up, with all needed columns...

i can post the code if you interested..

rhunt SSCertifiable Points: 5868 More actions · Answer 5

Yes, I would like that. I haven't really used cursors so I would love to see how its done.

Thanks

Ryan

Borik Ten Centuries Points: 1048 More actions · Answer 6

This is very Dinamick Pivot Cration... AND might not be very optimized... Your comments and sugestions are Welcome...

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

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_Pivot]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Begin

--drop table [dbo].[t_Pivot]

Print 'Table Exists!!!'

End

ELSE

Begin

CREATE TABLE [dbo].[t_Pivot] ( [c_Id] [numeric](10, 0) NOT NULL ) ON [PRIMARY]

End

GO

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

declare @sql varchar(2048);

declare @count int;

declare @i int; Select @i=0;

--

DECLARE @curentId INT;Select @curentId = -1;

DECLARE @Id INT;

DECLARE @key varchar(255); DECLARE @value varchar(255);

DECLARE c CURSOR FOR

SELECT c_Id, c_key, c_value FROM dbo.t_Table where c_Id>41000 and c_Id<=41900 order by c_Id

OPEN c

FETCH NEXT FROM c into @Id, @key, @value

WHILE @@FETCH_STATUS = 0

BEGIN

IF(@curentId @Id)

BEGIN

Select @i=@i+1;

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ';

print @i

print @Id;

print '~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ';

SELECT @curentId=@id

INSERT INTO t_Pivot (c_Id) VALUES (@curentId);

END

select @count=count(*) from INFORMATION_SCHEMA.COLUMNS where column_name=@key and table_name='t_Pivot'

IF @count=0 -- No Column

BEGIN

PRINT '--------------------- Adding Column: '+@key +' and value: '+@value;

SET @sql = 'ALTER TABLE t_Pivot ADD ['+ @key +'] VARCHAR('+STR(LEN(@value))+') NULL';

EXEC (@sql);

SET @sql = 'UPDATE t_Pivot SET ['+@key+'] = '''+@value+''' WHERE c_Id = '+STR(@curentId);

EXEC (@sql);

END

ELSE

BEGIN

PRINT ' Update the Column: '+@key +' with value: '+@value;

IF (DATALENGTH(@value) > COL_LENGTH('t_Pivot',@key))-- value bigger then Column

BEGIN

print '--------------------- making Column bigger: ' + Str(LEN(@value)) +' - '+ str(COL_LENGTH('t_Table',@key));

SET @sql = 'ALTER TABLE t_Pivot ALTER COLUMN ['+ @key +'] VARCHAR('+STR(LEN(@value))+') NULL';

EXEC (@sql);

END

SET @sql = 'UPDATE t_Pivot SET ['+@key+'] = '''+@value+''' WHERE c_Id = '+STR(@curentId);

EXEC (@sql);

END

FETCH NEXT FROM c into @Id, @key, @value

END

CLOSE c

DEALLOCATE c

print 'FINISH!!! ';

Please Let Me know What You think...