Help On Query
-
Hi Everybody,
Create Table #sample
( Name Varchar(100),
Mark1 int,
Mark2 int,
Mark3 int)
insert into #sample values ('Vignesh',100,59,95)
insert into #sample values ('ram',23,45,33)
insert into #sample values ('kumar',58,12,15)
insert into #sample values ('umar',15,25,98)
I need following output ...
select name,mark1,'mark1' from #sample
union all
select name,mark2,'mark2' from #sample
union all
select name,mark3,'mark3' from #sample
Is there any dynamic query to produce this output?
-
Why does it have to be dynamic?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
column may change time to time
-
Here is the pattern for a unpivot operation:
SELECT s.Name, CASE n.n WHEN 1 THEN s.Mark1WHEN 2 THEN s.Mark2
WHEN 3 THEN s.Mark3
END AS Mark,
CASE n.n WHEN 1 THEN 'Mark1'
WHEN 2 THEN 'Mark2'
WHEN 3 THEN 'Mark3'
END AS MarkName
FROM #sample s
CROSS JOIN (VALUES (1), (2), (3)) AS n(n)
ORDER BY s.Name, n.n
If you want things to be "dynamic" this indicates that you have a flaw somewhere earlier in the process. If you tell us the full story, we may be able to point out there.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
If you are trying to make the column names and number of columns dynamic, you could try something like:
DECLARE @TempTableName AS NVARCHAR(128)
, @FirstColumnName AS NVARCHAR(128)
, @ValueColumnAlias AS NVARCHAR(128)
SET @TempTableName = '#sample'
SET @FirstColumnName = 'Name'
SET @ValueColumnAlias = 'Mark'
DECLARE @sql AS NVARCHAR(MAX)
, @x AS INT
SET @x = 0
SET @sql = ''
SELECT @sql = @sql + CASE WHEN @x = 0
THEN 'SELECT '+ @FirstColumnName + ', '
ELSE 'UNION ALL SELECT '+ @FirstColumnName + ', '
END + tempdb.sys.columns.name + ' AS ' + @ValueColumnAlias + ', ' + CHAR(39) + tempdb.sys.columns.name + char(39)
+ ' AS ColumnName FROM ' + @TempTableName
+ CHAR(13)
, @x = @x + 1
FROM tempdb.sys.columns
JOIN tempdb.sys.tables
ON tempdb.sys.tables.object_id = tempdb.sys.columns.object_id
WHERE tempdb.sys.tables.object_id = OBJECT_ID('tempdb..'+@TempTableName)
AND tempdb.sys.columns.name <> @FirstColumnName
SELECT @sql = @sql + 'ORDER BY ' + @FirstColumnName + ', ColumnName'
--PRINT @sql
EXEC(@SQL)
-
You say "I need following output ..." but then you show a query. So what output are you trying to get from querying that data?
-
There is a problem with that solution: it relies on undefined behaviour. That is, there are no guarantees that SELECT @sql = @sql + col with produce what you expect. Adding @x into the mix does not help.
Another flaw is that you do not use quotename, this is absolute necessary when building solutions that is to handle dynamic table and column names, as they could be used for SQL injection.
Here is a solution that uses FOR XML PATH, which is a well-defined way to create a concatenated string - nevermind that the syntax is obscure for the purpose.
But going back to my original solution, I don't like my solution either. I simply believe that it solves the wrong problem.
Create Table #sample( Name Varchar(100),
Mark1 int,
Mark2 int,
Mark3 int)
insert into #sample values ('Vignesh',100,59,95)
insert into #sample values ('ram',23,45,33)
insert into #sample values ('kumar',58,12,15)
insert into #sample values ('umar',15,25,98)
DECLARE @TempTableName AS NVARCHAR(128)
, @FirstColumnName AS NVARCHAR(128)
, @ValueColumnAlias AS NVARCHAR(128)
SET @TempTableName = '#sample'
SET @FirstColumnName = 'Name'
SET @ValueColumnAlias = 'Mark'
DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql =
(SELECT CASE WHEN rowno = 1
THEN 'SELECT '+ @FirstColumnName + ', '
ELSE 'UNION ALL SELECT '+ @FirstColumnName + ', '
END +
quotename(name) + ' AS ' + quotename(@ValueColumnAlias) + ', ' +
quotename(name, '''') + ' AS ColumnName FROM ' + quotename(@TempTableName)
FROM (SELECT c.name, row_number() OVER (ORDER BY c.column_id) AS rowno
FROM tempdb.sys.columns c
JOIN tempdb.sys.tables t ON t.object_id = c.object_id
WHERE t.object_id = OBJECT_ID('tempdb..'+@TempTableName)
AND c.name <> @FirstColumnName) AS x
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
SELECT @sql = @sql + 'ORDER BY ' + quotename(@FirstColumnName) + ', ColumnName'
PRINT @sql
EXEC(@sql)
go
DROP TABLE #sample
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
-
Erland,
In case you didn't notice, I said the solution could be "something like." I was not offering a "production" solution. The original question was not specific enough. I was just trying to show that a solution could be more dynamic that the one you offered.
And, I do agree with some of your critique.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply