MS SQL Transpose Like Excel Transpose (Dynamic SQL)

Question

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

What is the purpose of this expression? `CONCAT('SAMPLE_', ROW_NUMBER() OVER( ORDER BY (SELECT 1) ) ) AS AAA,` - it doesn't do anything useful. — Dai, Jun 16 '21 at 15:15
Transposing data from rows to columns for the purposes of displaying data to the user should generally **not** be done in SQL because it's an presentation-layer concern, not a data-access concern. Please tell us exactly why are you wanting to `PIVOT` in SQL. Doing so essentially makes the output data useless for consumption by other programs. If you want to consume transposed SQL data in Excel then you should use a macro in Excel to transpose it after it's loaded. — Dai, Jun 16 '21 at 15:16
Do you "have" to do this in SQL Server? This is really something for your presentation layer, *not* the RDBMS. — Thom A, Jun 16 '21 at 15:17
I tried to use AAA for transposed table's column name. It isn't a "must have" column. I tried to use it as dummy column name. — Emre, Jun 16 '21 at 15:18
Then consumable sample data, *not* images, will help us help you. Take the time to post DDL and DML statements for your table and data. — Thom A, Jun 16 '21 at 15:23
The biggest problem you have here, however, is you are mixing data types. `'Active'` is **not** a numerical value, but your column `CUS_ID` is clearly an `int`, and you can't mix data types in a column. So, again, I strongly suggest you rethink your requirements and move the logic elsewhere. — Thom A, Jun 16 '21 at 15:25
Our department needs sample values for each tables. For ex: Top 10 samples. Unfortunately we don't have a micro design or similar document. My ultimate goal is that I get 10 samples from every table and consalidate with "union all". If I get one table's transposed solution, I added while loop for every table. — Emre, Jun 16 '21 at 15:30
If they need sample data, why pivot it? Non-pivoted is going to be *far* more useful as the data will be strongly typed. — Thom A, Jun 16 '21 at 15:33
[Edit] your question, @Emre , but *we* can't run that SQL, so it's not helpful. — Thom A, Jun 16 '21 at 15:38

John Cappelletti · Accepted Answer · 2021-06-16T17:06:25.657

0

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

JUST For FUN

You could also supply a query as the @Source

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

edited Jun 16 '21 at 17:06

answered Jun 16 '21 at 15:46

John Cappelletti

79,615
7
44
66

Thank you very much. This is what I need. But I have an error. Incorrect syntax near 'Without_Array_Wrapper'. – Emre Jun 16 '21 at 17:07
@Emre Are you not on 2016+ ? – John Cappelletti Jun 16 '21 at 17:08
Hi, Yes it is SQL Server 2016 (Ver: 13.0.16106.4) – Emre Jun 16 '21 at 17:10
Take a second look at the dbFiddle https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=3d7c3351376d1c002fb86878fbba2ee3 This is 2016 and no errors – John Cappelletti Jun 16 '21 at 17:13
I only copy and paste. I didn't change even a ".". My messages is: (12 row(s) affected) (12 row(s) affected) Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'Without_Array_Wrapper'. – Emre Jun 16 '21 at 17:14
@Emre Just did a clean copy/paste from this answer to dbFiddle ... No errors. – John Cappelletti Jun 16 '21 at 17:17
Hi, As you say at dbFiddle it works perfectly but I think there a "something" at my SQL Mngt. Best regards. I will reserch it. Thank you very very much. – Emre Jun 16 '21 at 17:23
Do you have your previous codes? Maybe the previous ones works on me. – Emre Jun 16 '21 at 17:24
@Emre It was always the JSON approach. Only minor tweaks to in making the SOURCE a variable. Perhaps check your Compatibility Level https://stackoverflow.com/questions/1501596/how-to-check-sql-server-database-compatibility-after-sp-dbcmptlevel-is-deprecate – John Cappelletti Jun 16 '21 at 17:28
Great. Now I saw that my SQL version is 2014. – Emre Jun 16 '21 at 17:33
@Emre There is an XML approach. Give me a moment – John Cappelletti Jun 16 '21 at 17:34
@Emre Added an XML approach. Also, if you are truly on 2016 with a Compatibility Level of 2014 ... check with your DBA. It could have been inherited when you migrated from 2014 to 2016 – John Cappelletti Jun 16 '21 at 17:47

MS SQL Transpose Like Excel Transpose (Dynamic SQL)

1 Answers1