Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to do pivot Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 2:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 5:50 AM
Points: 2, Visits: 41
Hi Guys,

actually my table data looks

Key value
firstname surya
firstname rakhi
firstname venkat
firstname shankar
firstname dany

I want to disply like
firstname
surya
rakhi
venkat
shankar
dany


i write the below query

SELECT firstname
FROM test pvt
PIVOT (MAX(pvt.value ) FOR pvt.key1 IN (firstname)) AS P

but it showing

firstname
venkat


Please help to solve this
Post #1443149
Posted Wednesday, April 17, 2013 3:38 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:36 AM
Points: 711, Visits: 2,211
Hi,

I haven't used PIVOT because this can be solved with a CASE statement.

DECLARE @table TABLE
(
[Key] VARCHAR(50),
Value VARCHAR(50)
)

INSERT INTO @table
SELECT 'firstname', 'surya'
UNION ALL SELECT 'firstname', 'rakhi'
UNION ALL SELECT 'firstname', 'venkat'
UNION ALL SELECT 'firstname', 'shankar'
UNION ALL SELECT 'firstname', 'dany'

SELECT
CASE WHEN [key] = 'firstname' THEN Value END AS FirstName
FROM
@table
ORDER BY
CASE WHEN [key] = 'firstname' THEN Value END

Hope that helps.

Thanks,

Simon




MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1443158
Posted Wednesday, April 17, 2013 3:45 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:41 AM
Points: 616, Visits: 3,581
even easier ;)

DECLARE @table TABLE
(
[Key] VARCHAR(50),
Value VARCHAR(50)
)

INSERT INTO @table
SELECT 'firstname', 'surya'
UNION ALL SELECT 'firstname', 'rakhi'
UNION ALL SELECT 'firstname', 'venkat'
UNION ALL SELECT 'firstname', 'shankar'
UNION ALL SELECT 'firstname', 'dany'

SELECT
[value] AS FirstName
FROM
@table
ORDER BY
[Value]

Post #1443160
Posted Wednesday, April 17, 2013 3:47 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:36 AM
Points: 711, Visits: 2,211
I suspect that the OP has more than one "key" name!



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1443162
Posted Wednesday, April 17, 2013 3:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 1,037, Visits: 7,028
Easier and Correcter

SELECT [firstname] = value
FROM test
WHERE key = 'firstname'




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1443164
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse