Convert row to column and apply sorting using sql or c#

Question

I have requirement of sorting dynamically made column from row. I have following structure of data in SQL :

All attributes are treated as column but actually it stored in DB as row and their respective TextValue(If type is Text),DateValue (if type is date time or date)

  Id | TextValue | DateValue | Attribute
  --------------------------------------------
  1  | abc       | -         | SiteLocation
  2  | -         | 1-1-2013  | Holiday date
  3  | xyz       | -         | SiteLocation
  4  | -         | 2-2-2014  | Holiday date
  5  | pqr       | -         | SiteLocation
  6  | abc       | -         | SiteLocation 

I want to apply sorting on SiteLocation and I am displaying it as column. So how can i achieve this

SiteLocation | Holiday date
abc          | -
-            | 1-1-2013
xyz          |-
-            |2-2-2014
pqr          |-
abc          |-

I want to apply sorting on SiteLcoation or Holiday date in UI grid. Please suggest me some way how can I do it?

Answer 1

Here is your table

CREATE TABLE #TEMP(Id INT,TextValue VARCHAR(100),DateValue DATE,Attribute VARCHAR(100))

INSERT INTO #TEMP
SELECT 1  Id, 'abc' TextValue        ,NULL DateValue         ,'SiteLocation' Attribute
UNION ALL
SELECT   2  ,NULL         ,'1-1-2013'  ,'Holiday date'
UNION ALL
SELECT   3  ,'xyz'       ,NULL         ,'SiteLocation'
UNION ALL
SELECT   4  , NULL        ,'2-2-2014'  ,'Holiday date'
UNION ALL
SELECT  5  ,'pqr'       ,NULL         ,'SiteLocation'
UNION ALL
SELECT   6  ,'abc'       ,NULL         ,'SiteLocation' 

QUERY

SELECT [SiteLocation],[Holiday DATE]
FROM 
(
   SELECT ID,ISNULL(TextValue,DateValue) VALUE,Attribute
    FROM #TEMP
)P
PIVOT 
(
    min(VALUE) FOR
    Attribute IN ([SiteLocation],[Holiday DATE])
)
AS i

• SQL FIDDLE

UPDATE

I am updating the query as you suggested.

Here you will select the columns for converting rows to columns

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + [Attribute] + ']', 
               '[' + [Attribute] + ']')
               FROM    (SELECT DISTINCT [Attribute] FROM #TEMP) PV  
               ORDER BY [Attribute]

Now you can pivot dynamically here.

DECLARE @query NVARCHAR(MAX)
SET @query = '        
              -- Your pivoted columns will be displayed   
              SELECT ' + @cols + ' FROM 
             (
                 -- Combine into single column
                 SELECT ID,ISNULL(TextValue,DateValue) VALUE,Attribute
                 FROM #TEMP
             ) x
             PIVOT 
             (
                 MIN(VALUE)
                 FOR [Attribute] IN (' + @cols + ')
            ) p      

            '     
EXEC SP_EXECUTESQL @query

• SQL FIDDLE