CODEWITHSUNDEEP
sql-serverpivot
shiba
shiba

Reputation: 1

display sql table rows as columns

I have One table called NewDemo.

ID  Name     Condition  Value
--  ----     ---------  -----
1   Book     Amount     90/-
2   Pencil   Amount      5/-
3   Eraser   Amount     7/-
1   Book     Quantity   900
2   Pencil   Quantity   280
3   Eraser   Quantity   200

But I want to Display like Below

ID    Name     Amount     Quantity
--    ----     ------     --------
1     Book     90/-       900
2     Pencil   5/-        280
3     Eraser   7/-        200

Get me some solutions.../ Thank You All

Upvotes: 0

Views: 117

Answers (4)

Angelo
Angelo

Reputation: 335

hi I think this will help you

SELECT     ID, Name, MAX(DISTINCT CASE WHEN Condition = 'Amount' THEN Value END) AS   Amount, MAX(DISTINCT CASE WHEN Condition = 'Quantity' THEN Value END) 
           AS Quantity
FROM       dbo.TESTTABLE
GROUP BY   ID, Name

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

You can do something like this.

SELECT A.ID, A.Name, A.Value AS 'Amount', B.Value AS 'Quantity'
FROM (SELECT * FROM NewDemo WHERE Condition = 'Amount') AS A
    JOIN (SELECT * FROM NewDemo WHERE Condition = 'Quantity') AS B
    ON A.ID = B.ID

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

in sql-server :: try this

select * from [dbo].[2]
pivot
(
max(value)
for condition in([amount],[quantity]) 
)as piv order by id

Upvotes: 2

Kapil Jain
Kapil Jain

Reputation: 188

SELECT a.ID, a.Name, a.Value as Amount, b.Value as Quantity FROM 
(SELECT ID, Name, Value FROM NewDemo WHERE Condition = 'Amount') a 
LEFT JOIN (SELECT ID, Name, Value FROM NewDemo WHERE Condition = 'Quantity') b 
on a.ID = b.ID

Upvotes: 0

Related Questions