Search code examples
sql-serverdynamicpivottranspose

MS SQL Transpose Like Excel Transpose (Dynamic SQL)

I need a dynamic SQL code similar to the excel transpose operation. I have tried transpose a table with Dynamic SQL. I have tried Pivot/ XML, Dynamic SQL. Collate .I have failed. I tried CAST(column collate database_default AS NVARCHAR(MAX)) conversions.

Where am I doing wrong? How can I write the code?

My ultimate goal is to create a composite table that is obtained as a result of the following query. As you can see, sample columns are empty in this table. I want to do a “while loop” query to populate these values. Before creating the loop, I need a code similar to the excel transpose operation. With this operation I can copy the results to the composite table.

SELECT A.TABLE_CATALOG, A. TABLE_SCHEMA, B.COLUMN_CNT,B.DUM_TABLE_POSITION,
A.ORDINAL_POSITION AS DEF_COLUMN_POSITION, A.TABLE_NAME, A.COLUMN_NAME,
SAMPLE_1=NULL,SAMPLE_2=NULL,SAMPLE_3=NULL,SAMPLE_4=NULL,SAMPLE_5=NULL,
SAMPLE_6=NULL,SAMPLE_7=NULL,SAMPLE_8=NULL,SAMPLE_9=NULL,SAMPLE_10=NULL
FROM INFORMATION_SCHEMA.COLUMNS A,
(SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,COUNT(*) AS COLUMN_CNT,
ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS DUM_TABLE_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
HAVING COUNT(*)>0 ) B
WHERE A.TABLE_CATALOG=B.TABLE_CATALOG
AND A.TABLE_SCHEMA=B.TABLE_SCHEMA
AND A.TABLE_NAME=B.TABLE_NAME
AND A.TABLE_CATALOG='DWH_PROD'
AND A.TABLE_SCHEMA='dbo'
AND A.TABLE_NAME IN (N'DWH_PROD.dbo.MY_TABLE_1', N'DWH_PROD.dbo.MY_TABLE_2')

enter image description here

.............................

Sample Data

CREATE TABLE #temporary_table 
(CUS_ID INT, 
TITLE NVARCHAR (50), 
PROMOTER NVARCHAR (50), 
CUS_STATUS NVARCHAR (50))
;

INSERT INTO #temporary_table 
VALUES
(11,'A',NULL,'PASSIVE'),
(22,'B',NULL,'ACTIVE'),
(33,'D',NULL,'ACTIVE'),
(44,'B',NULL,'ACTIVE'),
(55,'B',NULL,'ACTIVE'),
(66,'C',NULL,'ACTIVE'),
(77,'D',NULL,'ACTIVE'),
(88,'D',NULL,'ACTIVE'),
(101,'D',NULL,'ACTIVE'),
(123,'D',NULL,'ACTIVE'),
(200,'D',NULL,'ACTIVE'),
(300,'A',NULL,'PASSIVE')
;

SELECT TOP 10 CONCAT('SAMPLE_', ROW_NUMBER() OVER( ORDER BY (SELECT 1) ) ) AS AAA,*
FROM #temporary_table

I can get results separately with a single code below. But this result does not meet my request.

DECLARE @sql nvarchar(max) = N'';
SELECT @sql += N'
SELECT TOP (10) [table] = N''' + REPLACE(name, '''','') + ''', * 
FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
+ '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t
WHERE name IN (N'DWH_PROD.dbo.MY_TABLE_1', N'DWH_PROD.dbo.MY_TABLE_2')
EXEC sys.sp_executesql @sql;

EDIT:::::::::::::::::::::::::::

Great Thanks to the @John Cappelletti I have nearly finished the query. The code works on Server 2014. I didn't test all other scenarios. If there is only NULL values for first 10 rows for a column the second Table name will be seemed as NULL.

-- DROP TABLE #HamdullahUstadKacincidir2
-- DROP TABLE #HamdullahUstadKacincidir
CREATE TABLE #HamdullahUstadKacincidir2 
(COLUMN_NAME NVARCHAR (MAX),
SAMPLE_1 NVARCHAR (MAX),
SAMPLE_2 NVARCHAR (MAX),
SAMPLE_3 NVARCHAR (MAX),
SAMPLE_4 NVARCHAR (MAX),
SAMPLE_5 NVARCHAR (MAX),
SAMPLE_6 NVARCHAR (MAX),
SAMPLE_7 NVARCHAR (MAX),
SAMPLE_8 NVARCHAR (MAX),
SAMPLE_9 NVARCHAR (MAX),
SAMPLE_10 NVARCHAR (MAX))

CREATE TABLE #HamdullahUstadKacincidir 
(TABLE_NAME NVARCHAR (MAX),
COLUMN_NAME NVARCHAR (MAX),
SAMPLE_1 NVARCHAR (MAX),
SAMPLE_2 NVARCHAR (MAX),
SAMPLE_3 NVARCHAR (MAX),
SAMPLE_4 NVARCHAR (MAX),
SAMPLE_5 NVARCHAR (MAX),
SAMPLE_6 NVARCHAR (MAX),
SAMPLE_7 NVARCHAR (MAX),
SAMPLE_8 NVARCHAR (MAX),
SAMPLE_9 NVARCHAR (MAX),
SAMPLE_10 NVARCHAR (MAX))


DECLARE @SourceTableName AS NVARCHAR (MAX)
DECLARE @SourceTableSql AS NVARCHAR (MAX)
DECLARE @SourceTableSql22 AS NVARCHAR (MAX)
DECLARE @SourceTableSql333 AS NVARCHAR (MAX)
DECLARE @TableI INT
DECLARE @TableIN NVARCHAR(MAX)
DECLARE @SampleSize INT
DECLARE @TableCount INT 
SET @TableI=1
SET @SampleSize= 10
SET @TableCount= (SELECT COUNT(*) AS MAX_TABLE_CNT
FROM
(SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,COUNT(*) AS COLUMN_CNT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE CONCAT(TABLE_CATALOG,'.', TABLE_SCHEMA,'.', TABLE_NAME) NOT IN --Has at Least One Geography Data_Type Column
(SELECT CONCAT(C.TABLE_CATALOG,'.', C.TABLE_SCHEMA,'.', C.TABLE_NAME) AS TABLE_HAS_GEOGRAPHY_DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE DATA_TYPE='Geography')
AND CONCAT(TABLE_CATALOG,'.', TABLE_SCHEMA,'.', TABLE_NAME) NOT IN --Eliminate Empty Tables
(SELECT CONCAT(C.TABLE_CATALOG,'.', C.TABLE_SCHEMA,'.', C.TABLE_NAME) AS EMPTY_TABLE
FROM sys.tables t
join sys.schemas s on (t.schema_id = s.schema_id)
join sys.partitions p on (t.object_id = p.object_id)
join INFORMATION_SCHEMA.COLUMNS c on (s.name=c.TABLE_SCHEMA and t.name=c.TABLE_NAME)
WHERE p.index_id in (0,1)
GROUP BY C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME
HAVING SUM(p.rows) = 0)
AND CONCAT(TABLE_CATALOG,'.', TABLE_SCHEMA,'.', TABLE_NAME) NOT IN --Black List Tables
('DWH_PROD.dbo._tmp_retro')
GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME) T ) 

WHILE @TableI<=@TableCount
BEGIN
SET @TableIN=CAST(@TableI AS NVARCHAR(MAX))
SET @SourceTableSql=
'SELECT @SourceTableTemp=CONCAT(TABLE_CATALOG,''.'',TABLE_SCHEMA,''.'',TABLE_NAME)
FROM
(SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,COUNT(*) AS COLUMN_CNT,
ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS DUM_TABLE_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CONCAT(TABLE_CATALOG,''.'', TABLE_SCHEMA,''.'', TABLE_NAME) NOT IN --Has at Least One Geography Data_Type Column
(SELECT CONCAT(C.TABLE_CATALOG,''.'', C.TABLE_SCHEMA,''.'', C.TABLE_NAME) AS TABLE_HAS_GEOGRAPHY_DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE DATA_TYPE=''Geography'')
AND CONCAT(TABLE_CATALOG,''.'', TABLE_SCHEMA,''.'', TABLE_NAME) NOT IN --Eliminate Empty Tables
(SELECT CONCAT(C.TABLE_CATALOG,''.'', C.TABLE_SCHEMA,''.'', C.TABLE_NAME) AS EMPTY_TABLE
FROM sys.tables t
join sys.schemas s on (t.schema_id = s.schema_id)
join sys.partitions p on (t.object_id = p.object_id)
join INFORMATION_SCHEMA.COLUMNS c on (s.name=c.TABLE_SCHEMA and t.name=c.TABLE_NAME)
WHERE p.index_id in (0,1)
GROUP BY C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME
HAVING SUM(p.rows) = 0)
AND CONCAT(TABLE_CATALOG,''.'', TABLE_SCHEMA,''.'', TABLE_NAME) NOT IN --Black List Tables
(''DWH_PROD.dbo._tmp_retro'')
GROUP BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME) K
WHERE DUM_TABLE_POSITION=@TableIN'

EXECUTE sp_executesql @SourceTableSql, N'@TableIN NVARCHAR(MAX),@SourceTableTemp NVARCHAR (MAX) OUTPUT', 
@TableIN=@TableIN, @SourceTableTemp = @SourceTableName OUTPUT

DELETE #HamdullahUstadKacincidir2

SET @SourceTableSql22 ='INSERT INTO #HamdullahUstadKacincidir2
SELECT *
FROM ( SELECT A.AAA,C.*
FROM ( SELECT TOP '+CONCAT('',@SampleSize)+' AAA = CONCAT(''SAMPLE_'', ROW_NUMBER() OVER( ORDER BY (SELECT 1) ) ),* 
FROM '+@SourceTableName+' SRC ) A
CROSS APPLY ( VALUES ((Select A.* FOR XML RAW,Type)) )B(XMLData)
CROSS APPLY ( SELECT COLUMN_NAME  = xAttr.value(''local-name(.)'', ''VARCHAR(MAX)''),VALUE = xAttr.value(''.'',''VARCHAR(MAX)'')
FROM XMLData.nodes(''//@*'') xNode(xAttr)
WHERE xAttr.value(''local-name(.)'', ''varchar(100)'')  not in (''AAA'')) C ) SRC
PIVOT (MAX(VALUE) FOR [AAA] IN (' + STUFF((SELECT TOP (@SampleSize) ','+CONCAT('SAMPLE_', ROW_NUMBER() OVER( ORDER BY (SELECT 1) )) 
FROM master..spt_values FOR XML Path('')),1,1,'')  + ') ) P'

EXECUTE(@SourceTableSql22)

INSERT INTO #HamdullahUstadKacincidir
SELECT TABLE_NAME=@SourceTableName,* FROM #HamdullahUstadKacincidir2

SET @TableI=@TableI+1

END 


SELECT E.*,K.*,H.*
FROM
(SELECT CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA,'.',TABLE_NAME) AS TABLE_NAME,COLUMN_NAME,DATA_TYPE,
ROW_NUMBER() OVER (PARTITION BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ORDER BY ORDINAL_POSITION ) AS COLUMN_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
) K
LEFT JOIN
#HamdullahUstadKacincidir H
ON K.TABLE_NAME COLLATE DATABASE_DEFAULT=H.TABLE_NAME COLLATE DATABASE_DEFAULT
AND K.COLUMN_NAME COLLATE DATABASE_DEFAULT=H.COLUMN_NAME COLLATE DATABASE_DEFAULT
LEFT JOIN
(SELECT CONCAT(C.TABLE_CATALOG,'.', C.TABLE_SCHEMA,'.', C.TABLE_NAME) AS EMPTY_TABLE
FROM sys.tables t
join sys.schemas s on (t.schema_id = s.schema_id)
join sys.partitions p on (t.object_id = p.object_id)
join INFORMATION_SCHEMA.COLUMNS c on (s.name=c.TABLE_SCHEMA and t.name=c.TABLE_NAME)
WHERE p.index_id in (0,1)
GROUP BY C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME
HAVING SUM(p.rows) = 0) E
ON K.TABLE_NAME=EMPTY_TABLE
ORDER BY K.TABLE_NAME,K.COLUMN_POSITION
Solution

  • Update - A more dynamic version. Just supply the table name (or query) and the SampleSize

    This will dynamically UNPIVOT your data and then PIVOT the results

    Example or dbFiddle

    Declare @Source     varchar(500) = '#temporary_table'
Declare @SampleSize int = 10

Declare @SQL varchar(max) = '
Select *
From (
        Select A.AAA 
              ,B.*
         From ( 
                SELECT TOP '+concat('',@SampleSize)+'
                       AAA = CONCAT(''SAMPLE_'', ROW_NUMBER() OVER( ORDER BY (SELECT 1) ) )
                      ,* 
                 FROM '+ @Source +' src
              ) A
         Cross Apply (
                        Select [Key]
                              ,Value
                         From OpenJson((Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES )) 
                         Where [Key] not in (''AAA'')
                     ) B
    ) src
 Pivot (max(Value) For [AAA] in (' + Stuff((Select top (@SampleSize) ','+CONCAT('SAMPLE_', ROW_NUMBER() OVER( ORDER BY (SELECT 1) )) From master..spt_values For XML Path('')),1,1,'')  + ') ) p
'

Exec(@SQL)

    Results

    enter image description here

    JUST For FUN

    You could also supply a query as the @Source

    Set @Source = '( Select * from [dbo].[ZIPCodes] where StateCode=''RI'') '