0

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?

Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
user2982644
  • 55
  • 6

1 Answers1

0

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

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
Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
  • Hey thank you very much for your replies. But one more clarification I need to do that there can be n number of attributes. So you can not always hard code in query like you have done here. SELECT [SiteLocation],[Holiday DATE] – user2982644 Jan 07 '15 at 09:22
  • I will update for that. I need to know I more thing - Will there be any more column than TextValue and DateValue? Why I asked is becz I need to know from where I need to take value for pivoting. @user2982644 – Sarath Subramanian Jan 07 '15 at 09:24
  • Is this what u r expecting? @user2982644 – Sarath Subramanian Jan 07 '15 at 15:02