0

Hi i am beginner and trying to get column header values in column such that it there values not equal to '0.0' or '0'

my query is,

select M1,M2,M3,M4,M5,M6,M7,M8,M9,M10 from [dbo].[Target]

Result of query is,

M1   M2   M3   M4   M5   M6   M7   M8   M9   M10
0    5.0  0.0  1.0  0.0  2.0  3.0  9.0  0    0.0

I am trying to get result in this format,

M2  5.0
M4  1.0
M6  2.0
M7  3.0
M8  9.0

column having "0" and "0.0" do not list in query result

Hopes for your suggestion thanks in Advance.

user3391397
  • 3
  • 2
  • man check out @M.Ali answer to my question a couple days ago. i had [same question](http://stackoverflow.com/questions/21911613/transpose-2-columns-multiple-rows-to-1-rows-multiple-columns) – Franck Mar 12 '14 at 11:46

1 Answers1

1

Something like:

SELECT ColName,Value
FROM dbo.Target t
UNPIVOT (Value for ColName in (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10)) u
WHERE Value != 0

See PIVOT and UNPIVOT. Result:

ColName               Value
--------------------- ----------------------
M2                    5
M4                    1
M6                    2
M7                    3
M8                    9
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448