Unpivot using Dynamic query

Question

I am writing a query to unpivot any table as required, based on it's first column and a selected row. And I am facing an issue in the dynamic query. The actual table has 77 columns with different data type that I tried to convert it to varchar and the values will always be of a particular row of a table. example:

CREATE TABLE EXAMPLE (ROWDATA INT, CHARDATA VARCHAR(MAX), BIGINTCOLUMN BIGINT)
INSERT INTO EXAMPLE (ROWDATA INT, CHARDATA VARCHAR(MAX), BIGINTCOLUMN BIGINT)
VALUES (1, AB, 15698), (2, BD, 23467), (3, ABC, 42378)

Now I want to do unpivot operation without putting columns so that I can use it for multiple purpose and there should only be 2 columns one is ColumnName that contains value
"CHARDATA,BIGINTCOLUMN" other ColumnValue that contains value like "BD,23467" or "ABC,42378" '

CODE:

create table #ColumnName (Countno int identity(1,1), Column_Name varchar(max))
insert into #ColumnName (Column_Name) select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'EXAMPLE'
Create table #Converttype (Countno int identity(1,1), Converttype varchar(max)) 
insert into #Converttype (Converttype) select case when DATA_TYPE <> 'varchar' then 'Cast(' + COLUMN_NAME + ' as varchar) as '+ COLUMN_NAME else COLUMN_NAME end from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'EXAMPLE'

declare @CreateTable varchar(max) =(select Column_Name from #ColumnName where Countno = 1)
declare @Converttype varchar(max) = (select Converttype from #Converttype where Countno = 1)    
declare @ColumnName varchar(max) = (select Column_Name from #ColumnName where Countno = 2)
declare @Iteration int = (select Count(COLUMN_NAME) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'EXAMPLE')
declare @count int = 2

while @Count <= @Iteration
begin
    --print @Count

    set @CreateTable = @CreateTable+' varchar(max)' + ',' + (select Column_Name from #ColumnName where Countno = @count) 
    set @Converttype = @Converttype + ',' + (select Converttype from #Converttype where Countno = @count)

    set @count = @count + 1
    set @ColumnName = @ColumnName + ',' + isnull((select Column_Name from #ColumnName where Countno = @count),'') 
end

declare @Unpivot nvarchar(max) = ' 
create table #temp (' + @CreateTable + ' varchar(max))
insert into #temp (ROWDATA,' + @ColumnName + ')
select ' + @Converttype + '  from EXAMPLE where ROWDATA = 2
select * from #temp
unpivot
(
    ColumnValue
    for ColumnName in (' + @ColumnName + ')
) AS Tablecheck'

Exec(@Unpivot)

After debugging I found out that @Unpivot is not working specially when I used print @Unpivot it doesn't show me the the message from select * from #temp to ) as Tablecheck

sacse · Accepted Answer · 2020-05-10 03:02:36Z

There is only a minor issue of the extra comma at the end of the @ColumnName otherwise your script works fine.

CREATE TABLE #ColumnName
(Countno     INT IDENTITY(1, 1), 
 Column_Name VARCHAR(MAX)
);
INSERT INTO #ColumnName(Column_Name)
       SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EXAMPLE';

CREATE TABLE #Converttype
(Countno     INT IDENTITY(1, 1), 
 Converttype VARCHAR(MAX)
); 
INSERT INTO #Converttype(Converttype)
       SELECT CASE
                  WHEN DATA_TYPE <> 'varchar'
                  THEN 'Cast(' + COLUMN_NAME + ' as varchar (max)) as ' + COLUMN_NAME
                  ELSE COLUMN_NAME
              END
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = 'EXAMPLE';



DECLARE @CreateTable VARCHAR(MAX)=
(
    SELECT Column_Name FROM #ColumnName WHERE Countno = 1
);
DECLARE @Converttype VARCHAR(MAX)=
(
    SELECT Converttype FROM #Converttype WHERE Countno = 1
);
DECLARE @ColumnName VARCHAR(MAX)=
(
    SELECT Column_Name FROM #ColumnName WHERE Countno = 2
);
DECLARE @Iteration INT=
(
    SELECT COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'EXAMPLE'
);

SELECT @Iteration

DECLARE @count INT= 2;
WHILE @Count <= @Iteration
    BEGIN
        print @Count

        SET @CreateTable = @CreateTable + ' varchar(max)' + ',' +
        (
            SELECT Column_Name FROM #ColumnName WHERE Countno = @count
        );
        SET @Converttype = @Converttype + ',' +
        (
            SELECT Converttype FROM #Converttype WHERE Countno = @count
        );
        SET @count = @count + 1;
        SET @ColumnName = @ColumnName + ',' + ISNULL(
        (
            SELECT Column_Name FROM #ColumnName WHERE Countno = @count
        ), '');
    END;



DECLARE @Unpivot NVARCHAR(MAX)= ' 
create table #temp (' + @CreateTable + ' varchar(max))
insert into #temp (ROWDATA,' + SUBSTRING(@ColumnName, 1, LEN(@COLUMNNAME) - 1) + ')
select ' + @Converttype + '  from EXAMPLE where ROWDATA >= 2
select * from #temp
unpivot
(
    ColumnValue
    for ColumnName in (' + SUBSTRING(@ColumnName, 1, LEN(@COLUMNNAME) - 1) + ')
) AS Tablecheck';

print (@unpivot)
EXEC (@Unpivot);

Please see the db<>fiddle here.

Collectives™ on Stack Overflow

Unpivot using Dynamic query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related