Select a value of a view with view name and column name

Question

What I have:

VIEW A 
uid name
10   test1
20   test2
30   test3
40   test4

What I want:

ViewName  ColName Row Value   
View A    uid     1   10
View A    name    1   test1
View A    uid     2   20
View A    name    2   test2

GMB · Accepted Answer · 2019-10-02T07:42:49.287

2

You can assign row numbers in a CTE and then unpivot with UNION ALL.

Notes:

uid looks like an integer datatype while name is a string, so you need to convert uid to a VARCHAR else UNION ALL will raise an error.
obviously the ViewName is harcoded in the query to viewA

Query:

WITH cte AS (
    SELECT 'viewA' ViewName, uid, name, ROW_NUMBER() OVER(ORDER BY uid) rn FROM viewA
)
SELECT ViewName, 'uid' ColName, rn Row, CAST(uid AS varchar(50)) value FROM cte
UNION ALL SELECT ViewName, 'name' ColName, rn Row, name FROM cte
ORDER BY rn, ColName DESC

Demo on DB FIddle:

ViewName | ColName | Row | value
:------- | :------ | :-- | :------
viewA    | uid     | 1   | 10              
viewA    | name    | 1   | test1           
viewA    | uid     | 2   | 20              
viewA    | name    | 2   | test2           
viewA    | uid     | 3   | 30              
viewA    | name    | 3   | test3           
viewA    | uid     | 4   | 40              
viewA    | name    | 4   | test4

edited Oct 02 '19 at 07:42

answered Oct 02 '19 at 07:28

GMB

216,147
25
84
135

Why not put ViewName in cte as well? – Dan Sydner Oct 02 '19 at 07:37
Any chance that the viewname could be generic? e.g. when I want to do that for more then 1 view at the same time – PassionateDeveloper Oct 02 '19 at 07:41
@Kovu: not really, since the view name is used in the query too... You would need to use dynamic SQL, which seems beyond the scope of your original question. – GMB Oct 02 '19 at 07:44
dynamic SQL is onyl available in SPs rightß and a SP can't be called as View? – PassionateDeveloper Oct 02 '19 at 07:52

score 0 · Answer 2 · answered Oct 02 '19 at 07:20

you could use a union based on a subquery on view_A

select  'View A', 'uid' colname, row_num,  uid value
from (
SELECT 
  ROW_NUMBER() OVER(ORDER BY uid ASC) AS row_num, uid, name
from view_A

)
UNION ALL 
select  'View A', 'name', row_num,  name
from (SELECT 
  ROW_NUMBER() OVER(ORDER BY uid ASC) AS row_num, uid, name
from view_A)
ORDER BY row_num, colname

Pouya Kamyar · Answer 3 · 2019-10-02T08:23:32.743

0

you can use this :

DECLARE @ViewName NVARCHAR(64) = 'ViewA'
DECLARE @Query NVARCHAR(MAX) = ''

SET @Query = ' 
SELECT  ' + '''' + @ViewName + + '''' + ' AS ViewName,
        C.[name] AS ColName,
        V.[name] AS [Value],
        DENSE_RANK() OVER( ORDER BY V.[Uid] ) AS [Row]
FROM    ' + @ViewName + ' AS V
        INNER JOIN sys.columns AS C ON C.object_id = OBJECT_ID(' + '''' + @ViewName + '''' + ')'
        print @Query

EXECUTE SP_EXECUTESQL @Query

edited Oct 02 '19 at 08:23

answered Oct 02 '19 at 07:32

Pouya Kamyar

133
1
9

The apostroph is not escaped :) – PassionateDeveloper Oct 02 '19 at 08:14
Not working, way to much rows. I think your inner join has the wrong on clause – PassionateDeveloper Oct 02 '19 at 08:33

Select a value of a view with view name and column name

3 Answers3