0

I have a table MonitorLocationDetails which looks like below

lid LoclColumn  LocDescription
1   MP1         MP-1 descr
2   MP2         MP-2 descr
3   MainGate    Main Gate descr

I should get the rows of LocDescription and transpose it like pivot in sql server other versions. PIVOT not available in sql compact versions. The Pivoting should be based on the result of a sub query

SELECT LocColumn,LocDescription 
FROM MonitorLocationDetails 
WHERE LocColumn IN ('MP1','MP2','MainGate')

This works ok.

I have so far :

SELECT DISTINCT 
    (CASE WHEN P.LocColumn = 'MP1' THEN P.LocDescription ELSE '' END), 
    (CASE WHEN P.LocColumn = 'MainGate' THEN P.LocDescription ELSE '' END), 
    (CASE WHEN P.LocColumn = 'MP2' THEN P.LocDescription ELSE '' END) 
FROM (
    SELECT LocColumn,LocDescription 
    FROM MonitorLocationDetails 
    WHERE LocColumn IN ('MP1','MP2','MainGate')
) P

which yields:

column1     column2     column3
                        MP-2 descr
            Main Gate desc  
MP-1 descr

empty spaces even with DISTINCT used.. i did not give names for columns, cause i need the result just as follows in 1 row

MP-1 descr   Main Gate descr    MP-2 descr

Any one would you please assist?

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Nish
  • 19
  • 7

1 Answers1

0

You're close. You just have to put MAX before your CASE expression.

SELECT 
    MAX(CASE WHEN P.LocColumn = 'MP1' THEN P.LocDescription ELSE '' END), 
    MAX(CASE WHEN P.LocColumn = 'MainGate' THEN P.LocDescription ELSE '' END), 
    MAX(CASE WHEN P.LocColumn = 'MP2' THEN P.LocDescription ELSE '' END) 
FROM (
    SELECT LocColumn,LocDescription 
    FROM MonitorLocationDetails 
    WHERE LocColumn IN ('MP1','MP2','MainGate')
) P

You could also simplify your query:

;WITH MonitorLocationDetails(lid, LocColumn, LocDescription) AS(
    SELECT 1, 'MP1', 'MP-1 Descr' UNION ALL
    SELECT 2, 'MP2', 'MP-2 Descr' UNION ALL
    SELECT 3, 'MainGate', 'MainGate Descr'
)
SELECT
    MAX(CASE WHEN LocColumn = 'MP1' THEN LocDescription ELSE '' END), 
    MAX(CASE WHEN LocColumn = 'MainGate' THEN LocDescription ELSE '' END), 
    MAX(CASE WHEN LocColumn = 'MP2' THEN LocDescription ELSE '' END) 
FROM MonitorLocationDetails 
WHERE LocColumn IN ('MP1','MP2','MainGate')
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • your simplified one looks good, but gives me blank result. I can't yet upvote cause of not enough reputations. will mark it as answer.. but still curious about the 2nd sql by you – Nish Feb 27 '15 at 00:54
  • Really? Try again, I've added sample data this time. – Felix Pamittan Feb 27 '15 at 00:56
  • got it.. i mean works even without the sample. It was just the extra spaces which made the sql SDF viewer not recognizing it. so we dont even need to use DISTINCT. – Nish Feb 27 '15 at 12:08