2x10 to 1x10 pivot - 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)

2x10 to 1x10 pivot

Author

Topic

Davidis
Starting Member

4 Posts

Posted - 2009-01-13 : 08:26:56

im trying to change a table looking like the one below

phrase | replacement
--------------------
name | a
site | z
email | foo
...

to

name | site |email
-------------------
a | z | foo

this is needed to keep backwards compatability with older systems i have to work with. which stores all the variables as a table with one record and many fields.

My code ive tried is shown below but it does not work.


SELECT     *
FROM         
(
   SELECT     PARAM, DATA
   FROM          SYSTEM_DATA
) AS P 
PIVOT 
(
  DATA FOR PARAM IN
  (
   (SELECT PARAM FROM SYSTEM_PARAM)
   ) AS pvt;

Has anybody got any ideas on how to do this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 08:36:10


DECLARE @ParamList varchar(max),@sql varchar(max)

SELECT @ParamList=LEFT(t.u,LEN(t.u)-1)
FROM
(SELECT DISTINCT PARAM + ','
 FROM Table
 ORDER BY PARAM + ','
 FOR XML PATH('')
)t(u)

SET @Sql='SELECT     *
FROM         
(
   SELECT     PARAM, DATA
   FROM          SYSTEM_DATA
) AS P 
PIVOT 
(
  MAX(DATA) FOR PARAM IN
  (['+REPLACE(@ParamList,',','],[') +']) AS pvt'

EXEC (@sql)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 11:06:54

If you put Visakh's code into a VIEW then you can use view to provide backward compatibility but maintain current table for better design for future.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 11:21:09

I didn't know DECLARE are allowed in a VIEW.

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-13 : 11:25:21

v. funny

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-13 : 11:26:45

But I am at least correct.

E 12°55'05.63"
N 56°04'39.26"

Davidis
Starting Member

4 Posts

Posted - 2009-01-13 : 11:27:43

THANK YOU

Davidis
Starting Member

4 Posts

Posted - 2009-01-14 : 05:42:24

i would like to use this as a view if possible so if you can suggest an alternative set of code.

Davidis
Starting Member

4 Posts

Posted - 2009-01-14 : 05:43:24

if this is not possible i will just have to run two tables for now.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-15 : 06:03:11

If you need to keep the column names dynamic using a View is not straight forward (as Peso pointed out).
If you don't need a dynamic list of columns remove the variables and insert a column list in place of @Paramlist e.g. [col1],[col2] etc.
CREATE View vTest AS
SELECT *
FROM
(
SELECT [PARAM], DATA
FROM TABLENAME
) AS P

PIVOT (MAX(DATA) FOR [PARAM] IN
([col1],[col2],[col3]))pvt

----------------------------------------
If you need dynamic list of columns:
Depending on how apps /users access data here are a proc, a function and a view to read data in your older format.

Create procedure:
Create procedure [dbo].[pParam]

AS

DECLARE @ParamList varchar(max),@sql varchar(max)

SELECT @ParamList=LEFT(t.u,LEN(t.u)-1)
FROM
(SELECT DISTINCT [PARAM] + ','
FROM TABLENAME
ORDER BY [PARAM] + ','
FOR XML PATH('')
)t(u)

SET @Sql='SELECT *
FROM
(
SELECT [PARAM], DATA
FROM TABLENAME
) AS P
PIVOT
(
MAX(DATA) FOR [PARAM] IN
(['+REPLACE(@ParamList,',','],[') +'])) AS pvt'

EXECUTE (@sql)

To Create Function to exec proc:
CREATE FUNCTION fn_aNewTestFunctionName()

RETURNS TABLE
AS
RETURN
SELECT *
FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec [DatabaseName].dbo.pParam')
AS tbl

To Create View:
CREATE VIEW aTestView
AS
Select * from fn_aNewTestFunctionName()

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:17:41

Also see this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources