how to make vertical data to horizontal data in SQL (Dynamic column)

Question

Query :

SELECT Parameter_name,Data_Type, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'SP_Name'

Result :

Parameter_name            Data_Type              CHARACTER_MAXIMUM_LENGTH
--------------------------------------------------------------------------
@SubPortfolioCd           varchar                         3
@AsofDate                 varchar                         10
@CompareWith              varchar                         1
@CurrencyCd               varchar                         3

I want result as :

Column1            Column2      Column3         Column4           
---------------------------------------------------------------
@SubPortfolioCd    @AsofDate    @CompareWith    @CurrencyCd      
varchar            varchar      varchar         varchar
3                  10           1               3

Please help me to acheive the above result in SQL Server 2008... (dynamic column if it has a lot of parameters) Thanks

StackOverflow is not a ["plz give me the codez"](http://thedailywtf.com/Articles/plz-email-me-teh-codez.aspx) place. [Please read this article](http://whathaveyoutried.com) — Barranka, Sep 19 '14 at 04:48
i've try in my code its not working for dynamic column if it has a lot of parameters (or i have mistake in my pivot code) — Noob, Sep 19 '14 at 09:06

score 1 · Answer 1 · answered Sep 19 '14 at 04:47

1

This is the classic problem, where we can do cross apply and unpivot the data and then use PIVOT to convert it into horizontal data

SELECT [Col1],[Col2],[Col3]  FROM
(SELECT Parameter_name,Data_Type, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'SP_NAME'
)T
cross apply 
( select 'Col1', T.Parameter_name  union
  select 'Col2', T.Data_Type union
  select 'Col3', cast(T.CHARACTER_MAXIMUM_LENGTH as nvarchar(256))
) c(col,val)
pivot
( max(val) for col in ( [Col1],[Col2],[Col3] ) ) pvt

answered Sep 19 '14 at 04:47

radar

13,270
2
25
33

i don't know its work or not... i've try in my code its not working for dynamic column if it has a lot of parameters (or i have mistake in my pivot code) – Noob Sep 19 '14 at 09:04
if you have dynamic columns, then you need to use dynamic sql. you can reference this link http://stackoverflow.com/questions/18317732/sql-server-pivot-table-with-joins-and-dynamic-columns – radar Sep 19 '14 at 13:47

Aladin Hdabe · Accepted Answer · 2014-09-19T05:10:25.610

Try this

declare @TotalRows int

DECLARE @Rows Table
(
 RowIndex Int PRIMARY KEY IDENTITY(1,1),
 Parameter_name sysname,
 Data_Type nvarchar(128), 
 CHARACTER_MAXIMUM_LENGTH int 
)


insert into @Rows(Parameter_name,Data_Type, CHARACTER_MAXIMUM_LENGTH)
 SELECT   Parameter_name,Data_Type, CHARACTER_MAXIMUM_LENGTH
 FROM INFORMATION_SCHEMA.PARAMETERS
 WHERE SPECIFIC_NAME = 'SP_Name'
Select @TotalRows=@@ROWCOUNT

  Declare @MaxLen1 int,@MaxLen2 int
  select  @MaxLen1=Max(Len(Parameter_name)) , @MaxLen2=Max(Len(Data_Type))  from @Rows
  Declare @MaxColSize int
  Select @MaxColSize= Case When @MaxLen1>@MaxLen2 Then @MaxLen1 Else @MaxLen2 End


declare @SQL nvarchar(MAX)
set @SQL='Declare @Table Table(
RowIndex int'

declare @I int
set @I=0
While @I < @TotalRows
Begin   
  set @SQL=@SQL+', 
Column'+Cast(@I+1 as nvarchar(5)) + ' nvarchar('+Cast(@MaxColSize as nvarchar(3))+')'
  Set @I=@I+1
End
 set @SQL=@SQL+'
 )
 insert into @Table (RowIndex) values(1),(2),(3) '

Declare 
 @Parameter_name sysname,
 @Data_Type nvarchar(50), 
 @CHARACTER_MAXIMUM_LENGTH int
Declare @RowIndex int
set @RowIndex=1
While @RowIndex < @TotalRows+1
Begin   

   select @Parameter_name =Parameter_name ,@Data_Type =Data_Type ,@CHARACTER_MAXIMUM_LENGTH =CHARACTER_MAXIMUM_LENGTH from @Rows Where RowIndex=@RowIndex
   set @SQL=@SQL+'
Update @Table Set Column'+Cast(@RowIndex as nvarchar(5))+'='''+Replace(@Parameter_name,'''','''''')+''' Where RowIndex=1
Update @Table Set Column'+Cast(@RowIndex as nvarchar(5))+'='''+Replace(ISNULL(@Data_Type,''),'''','''''')+''' Where RowIndex=2
Update @Table Set Column'+Cast(@RowIndex as nvarchar(5))+'='+isNull(Cast(@CHARACTER_MAXIMUM_LENGTH as nvarchar(20)),'NULL')+' Where RowIndex=3'
 set @RowIndex=@RowIndex+1
End
set @SQL=@SQL+'
 Select * from @Table'

 exec sp_executesql @SQL

how to make vertical data to horizontal data in SQL (Dynamic column)

2 Answers2