SQL query about SELECT ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query about SELECT ?

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2011-04-06 : 03:59:51

Hi
I have a table like below.

id type price
1 Bill 100
2 Bank 50
3 Ticket 10

So i want to display like below

Bill Bank Ticket
100
50
10

How can i do with SQL query ?

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2011-04-06 : 04:02:36

Sorry for first posted. It should be like below
Bill Bank Ticket
100 --- ---
--- 50 ---
--- --- 10

McDebil
Starting Member

23 Posts

Posted - 2011-04-06 : 04:45:50

SELECT '---' AS Bill, '---' AS Bank, CAST(Price AS VARCHAR(5)) AS Ticket
FROM [dbo].[TestTable]
WHERE [type] = 'Ticket'
UNION
SELECT '---' AS Bill, CAST(Price AS VARCHAR(5)) AS Bank, '---' AS Ticket
FROM [dbo].[TestTable]
WHERE [type] = 'Bank'
UNION
SELECT CAST(Price AS VARCHAR(5)) AS Bill, '---' AS Bank, '---' AS Ticket
FROM [dbo].[TestTable]
WHERE [type] = 'Bill'
ORDER BY Bill DESC, Bank DESC

McDebil

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 05:29:04

I think you are looking this S

declare @q nvarchar(max),@p nvarchar(max)
select @q=coalesce(@q+ ',['+type+']','['+type+']') from TestTable
set @p=N'select * from (select [Price],type from TestTable ) p pivot
(MAX([price])
for type in('+@q+')) as pvt'
exec(@p)

Raghu'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources