SQL query Joining table itself

Question

I have a following table:

TICKER   DATE      SHAREPRICE 
--------------------------------
ABC     1.1.2015      50
XYZ     1.1.2015     100

etc.

I want to make a query, where the result is following:

DATE      PRICE(TICKER ABC) PRICE(TICKER XYZ)
--------------------------------------------
1.1.2015      50                  100

In excel with pivot it should be easy, but what I'm looking for is a sql query — Leo, Jun 08 '15 at 04:46
http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server, seems can answer your question. — Dika Arta Karunia, Jun 08 '15 at 05:33
I'm using oracle db express and sql developer. I have never heard about pivots in SQL, I need to take a look. Thank you. — Leo, Jun 08 '15 at 06:26

score 1 · Answer 1 · answered Jun 08 '15 at 04:50

Use PIVOT in SQL SERVER.

DECLARE @test AS TABLE(TICKER VARCHAR(10), DATE DATETIME, SharePrice  INT)

INSERT INTO @test
SELECT 'ABC', '1/1/2015', 50 UNION
SELECT 'XYZ', '1/1/2015', 100


SELECT Date, ABC AS [PRICE(TICKER ABC)], XYZ AS [PRICE(TICKER XYZ)]  FROM @test
PIVOT(MAX(SharePrice) FOR Ticker IN(ABC, XYZ)) AS A

score 0 · Answer 2 · answered Jun 08 '15 at 07:05

In TSQL you can write a query using dynamic pivot as:

DECLARE @cols AS NVARCHAR(MAX),@colsFinal AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ', ' + TICKER 
                    from test1
                    group by TICKER, [DATE]

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsFinal = STUFF((SELECT distinct ', isnull( ' + TICKER  +
                           ' ,0) as [PRICE (TICKER ' + TICKER +' )]'
                    from test1
                    group by TICKER, [DATE]

            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = N'SELECT [Date],' + @colsFinal + N' from 
             (
                select [Date], TICKER , SharePrice
                from test1 
            ) x
            pivot 
            (
                max(SharePrice)
                for TICKER in (' + @cols + N')
            ) p '


exec sp_executesql @query;

DEMO

SQL query Joining table itself

2 Answers2