Transpose a particular row to columns
-
I am seeing lot of examples of PIVOT to do this, but my problem is different.
I have a table like this
I want to select one row among this and get the result as
Any dynamic way of getting this? - mahrajmca - Wednesday, June 20, 2018 4:06 PMI am seeing lot of examples of PIVOT to do this, but my problem is different.
I have a table like this
I want to select one row among this and get the result as
Any dynamic way of getting this?Here is a quick example of the query to transpose the set
😎
USE TEEST;
GO
SET NOCOUNT ON;;WITH SAMPLE_DATA(CID,COL1,COL2,COL3,COL4,COL5) AS
(
SELECT 1,'A','B','C','D','E' UNION ALL
SELECT 2,'F','G','H','I','J'
)
SELECT
SD.CID
,X.[Name]
,X.[Value]
FROM SAMPLE_DATA SD
CROSS APPLY
(
SELECT 'COL1', SD.COL1 UNION ALL
SELECT 'COL2', SD.COL2 UNION ALL
SELECT 'COL3', SD.COL3 UNION ALL
SELECT 'COL4', SD.COL4 UNION ALL
SELECT 'COL5', SD.COL5
) X([Name],[Value]);To do this dynamically, one has to retrieve the metadata and construct the query in dynamic SQL code.
-
Assuming you have an identity on the input table, to identify the row (row_id) you could try something like this:
1. Identify the object_id:
select *
from sys.objects
where object_id = object_id('input_table')2. Get all the column names and put them into a table:
create table output_table ([name] varchar(50), [value] varchar(10))
insert into output_table([name])
select name from sys.columns where object_id = object_id(found at step1)
and [name] <> 'row_id'3. update the output_table created at Step 2 with the Values:
declare @sql nvarchar(max) = ''
select @sql = @sql + '' + char(10) + char(13)select @sql = @sql
+ ' update output_table '
+ ' set value = ' + name
+ ' from output_table
cross join tbl
where [name] = ''' + name + ''''
+' and row_id = 1'+ char(10) + char(13)
from output_table
exec( @sql) - ralu_k_17 - Thursday, June 21, 2018 1:39 AM
Please don't. That's a pretty inefficient way of doing it.
-
Good catch Luis 😉
😎
Without further information, all input is speculative and not accurate enough. - Luis Cazares - Thursday, June 21, 2018 7:56 AM
Hello Luis!
I didn't really understand what you meant by this. Are you referring strictly to the Identity column of the input table? Or the whole solution?
Also, if you have some time could you please explain a bit more as to why it is inefficient ? Or just direct me to some resource to read upon?Thank you so much!
- ralu_k_17 - Friday, June 22, 2018 1:40 AM
I'm referring to the whole solution. Basically, you're reading the table and writing to a new one once for every column. After that, you still need to read from that new table. If you look at Eirikur's example, the code is just reading the table once.
There's also the problem that it's only handling one row at a time (and that can be an issue). Once you need to define a specific ID, you need to change the way you call your dynamic SQL (for best practices).
The concatenation method that you used is inefficient and using the FOR XML PATH option is a better solution on versions before 2017 where STRING_AGG was included. Here's an article on that, but there might be others: https://blogs.msdn.microsoft.com/arvindsh/2016/06/27/the-aggregate-concatenation-t-sql-anti-pattern/Here's a dynamic version of Eirikur's code.
/*Creating sample data, this is not part of the solution*/
CREATE TABLE dbo.SampleData(
CID int NOT NULL,
COL1 varchar(15) NULL,
COL2 varchar(15) NULL,
COL3 varchar(15) NULL,
COL4 varchar(15) NULL,
COL5 varchar(15) NULL
);INSERT INTO dbo.SampleData(CID,COL1,COL2,COL3,COL4,COL5)
SELECT 1,'A','B','C','D','E' UNION ALL
SELECT 2,'F','G','H','I','J'
GO
/*Solution starts here*/
DECLARE @SQL NVARCHAR(MAX);SELECT @SQL = N'SELECT ' + NCHAR(10) +
N' SD.CID ' + NCHAR(10) +
N' ,X.[Name] ' + NCHAR(10) +
N' ,X.[Value] ' + NCHAR(10) +
N'FROM dbo.SampleData SD ' + NCHAR(10) +
N'CROSS APPLY ' + NCHAR(10) +
N'(' +
STUFF(( SELECT REPLACE( 'UNION ALL SELECT ''<<column_name>>'', SD.<<column_name>>' + CHAR(10), '<<column_name>>', name)
FROM sys.columns
WHERE object_id = object_id('dbo.SampleData', 'U')
and [name] <> 'CID'
FOR XML PATH(''), TYPE).value('./text()[1]', 'nvarchar(max)'), 1, 9, '') + N') X([Name],[Value])' + NCHAR(10) +
N'WHERE SD.CID = @Id;'
PRINT @SQL;
EXECUTE sp_executesql @SQL, N'@Id int', @Id = 1; -
Thank you Luis, for taking the time and explaining it. I understood now, and agree that given a large set of data, my solution would have painful performance, in comparison with yours
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply