Columns as Rows and Rows as Columns

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

General SQL Server Forums

New to SQL Server Programming

Columns as Rows and Rows as Columns

Author

Topic

cajina
Starting Member

15 Posts

Posted - 2008-09-03 : 18:58:25

I have this values in vertical form:

ID * NOMBRE_CALIFICATIVO * VALOR_CALIFICATIVO

363 * BASELICOR * TEQUILA
363 * MATERIALENVASE * ALUMINIO
363 * PAQUETE * N/A
363 * SABOR * TEQUILA
363 * SUBMARCA * ARTIC BAY
363 * TIPOENVASE * LATA
363 * TIPOFERTA * N/A
363 * TIPOPAQUETE * UNIDAD
363 * BASELICOR * VODKA
364 * MATERIALENVASE * ALUMINIO
364 * PAQUETE * N/A
364 * SABOR * CEREZA
364 * SUBMARCA * ARTIC BAY
364 * TIPOENVASE * LATA
364 * TIPOFERTA * N/A
364 * TIPOPAQUETE * UNIDAD

Column NOMBRE_CALIFICATIVO is where I have column names and in column VALOR_CALIFICATIVO there are respective values.

Now I want it to deploy in horizontal form like this:

ID * BASELICOR * MATERIALENVASE * PAQUETE * SABOR * SUBMARCA * TIPOENVASE * TIPOFERTA * TIPOPAQUETE
363 * TEQUILA * ALUMINIO * N/A * TEQUILA * ARTIC BAY * LATA * N/A * UNIDAD
364 * VODKA * ALUMINIO * N/A * CEREZA * ARTIC BAY * LATA * N/A * UNIDAD

So I made a function that returns a table in wich I use a cursor to find columns and insert values in each of their respective columns.
But it can´t be executed cause I can´t use sp_executesql into a function. Now, How can I execute next code without using the procedure sp_executesql.

I'll show you my SQL code:

Declare @calificativo varchar(200);
Declare @query nvarchar(700);

Insert @RTD_Horizontal
(ID)
Select Distinct ID
From dbo.RTD_Vertical;

Open nombrecalificativo;
Fetch NEXT from nombrecalificativo into @calificativo;

While @@FETCH_STATUS = 0
begin

Set @query=
'Update @RTD_Horizontal ' +
'set ' + @calificativo + ' = v.VALOR_CALIFICATIVO ' +
'from dbo.RTD_Vertical v ' +
'inner join ' +
'@RTD_Horizontal t ' +
'on ' +
'v.ID = t.ID ' +
'where v.NOMBRE_CALIFICATIVO = ''' + @calificativo + ''';';
Exec sp_executesql @query;

Fetch NEXT from nombrecalificativo into @calificativo;
end;

Is there anybody to help me? Please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 00:53:39

[code]SELECT ID,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='BASELICOR' THEN VALOR_CALIFICATIVO ELSE NULL END) AS BASELICOR,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='MATERIALENVASE' THEN VALOR_CALIFICATIVO ELSE NULL END) AS MATERIALENVASE,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='PAQUETE' THEN VALOR_CALIFICATIVO ELSE NULL END) AS PAQUETE,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='SABOR' THEN VALOR_CALIFICATIVO ELSE NULL END) AS SABOR,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='SUBMARCA' THEN VALOR_CALIFICATIVO ELSE NULL END) AS SUBMARCA,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='TIPOENVASE' THEN VALOR_CALIFICATIVO ELSE NULL END) AS TIPOENVASE,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='TIPOFERTA' THEN VALOR_CALIFICATIVO ELSE NULL END) AS TIPOFERTA,
MAX(CASE WHEN NOMBRE_CALIFICATIVO='TIPOPAQUETE' THEN VALOR_CALIFICATIVO ELSE NULL END) AS TIPOPAQUETE
FROM YourTable
GROUP BY ID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources