locked
sql query RRS feed

  • Question

  • Hi


    I have written a sql query but getting desire output
    I have a column "value" also coumn "MeasureName" in a table name "Fact" and Coreresponding to each value there is a measure name  ex
    value     MeasureName
    2             Length
    5             Height
    8             Lenght
    12            Area
    33           Length
    5.45        Height
    ect.....

    so i want my output to be
    Heightvalue      Lengthvalue 
     5                          2
    5.5                         8
                                 33 etc....
    how could it be achieved with sql query

    • Moved by Tom Phillips Tuesday, November 30, 2010 1:39 PM TSQL Question (From:SQL Server Database Engine)
    Tuesday, November 30, 2010 9:21 AM

Answers

  • Try:

    ;with cteHeight as (select Value, row_number() over (order by ID) as row from Fact where MeasureName = 'Height'),
    cteLength as (select Value, row_number() over (order by ID) as row 
    from Fact where MeasureName = 'Length')
    
    select c1.Value as HeightValue, c2.Value as LengthValue 
    from cteHeight c1 FULL JOIN cteLength c2 on c1.Row = c2.Row
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Ai-hua Qiu Friday, December 3, 2010 6:26 AM
    • Marked as answer by Ai-hua Qiu Tuesday, December 7, 2010 8:23 AM
    Tuesday, November 30, 2010 3:48 PM

All replies

  • Hi


    I have written a sql query but getting desire output
    I have a column "value" also coumn "MeasureName" in a table name "Fact" and Coreresponding to each value there is a measure name  ex
    value     MeasureName
    2             Length
    5             Height
    8             Lenght
    12            Area
    33           Length
    5.45        Height
    ect.....

    so i want my output to be
    Heightvalue      Lengthvalue 
     5                          2
    5.5                         8
                                 33 etc....
    how could it be achieved with sql query

     

    • Moved by Tom Phillips Tuesday, November 30, 2010 1:35 PM TSQL Question (From:SQL Server Database Engine)
    • Merged by Ai-hua Qiu Friday, December 3, 2010 6:30 AM
    Tuesday, November 30, 2010 7:27 AM
  • search PIVOT usage

    i suppose you have another column (say id) to identify which Length is related to which Height.

    select Length as LengthValue, Height as HeightValue from
    (values
    (2,'Length',1),
    (5,'Height',1),
    (8,'Length',2),
    (12,'Height',2),
    (33,'Length',3),
    (5.45,'Height',3)
    ) AS temp (value, MeasureName, id)
    pivot (max(value) for MeasureName in (Length, Height)) as pvt

     You need Sql Server 2008 to run the code

     


     

    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Proposed as answer by Chintak Chhapia Tuesday, November 30, 2010 10:24 AM
    Tuesday, November 30, 2010 9:45 AM
  • http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/dba1b82a-c1e4-4d68-b28c-4afba8e90aa0#13af8eb7-d8b2-411f-aaab-d677607df599
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, November 30, 2010 12:23 PM
  • Hello Mushtaq,

    Here is a sample sql script which might solve your problem.

    with cte as (
    	select
    		id,
    		L = case when measurename = 'Length' then value else null end,
    		H = case when measurename = 'Height' then value else null end
    	from Fact 
    ), c as (
    	SELECT * 
    	FROM 
    	(
    		SELECT
    			STUFF(
    				(
    				SELECT
    				 ',' + CAST(L as varchar(10))
    				FROM cte
    				FOR XML PATH('')
    				), 1, 1, ''
    			 ) As L,
    			STUFF(
    				(
    				SELECT
    				 ',' + CAST(H as varchar(10))
    				FROM cte
    				FOR XML PATH('')
    				), 1, 1, ''
    			 ) As H
    	) C
    )
    SELECT * 
    FROM dbo.Split((SELECT TOP 1 L FROM C),',') L
    full join dbo.Split((SELECT TOP 1 H FROM C),',') H
    on H.id = L.id
    

    Please note that the above script first concatenate string using sql XML PATH() and then split string using sql

    I hope that helps,

    Eralper


    SQL Server and T-SQL Tutorials
    My Personal Site
    Our true mentor in life is science
    Tuesday, November 30, 2010 2:56 PM
  • Try:

    ;with cteHeight as (select Value, row_number() over (order by ID) as row from Fact where MeasureName = 'Height'),
    cteLength as (select Value, row_number() over (order by ID) as row 
    from Fact where MeasureName = 'Length')
    
    select c1.Value as HeightValue, c2.Value as LengthValue 
    from cteHeight c1 FULL JOIN cteLength c2 on c1.Row = c2.Row
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Ai-hua Qiu Friday, December 3, 2010 6:26 AM
    • Marked as answer by Ai-hua Qiu Tuesday, December 7, 2010 8:23 AM
    Tuesday, November 30, 2010 3:48 PM