x

How to display 2 columns data in to rows?

Hello, I have a result set which disaplays 2 columns with 2 rows data.and i want to show it as a row.I know it colud be achieved by using pivot.but i tried something and unable to get the required result.please help me.

My raw query :

 SELECT TOP 2
   [PUBLICATION_ID],[ITEM_REFERENCE_ID]       
 FROM [Tridion_Broker].[dbo].[ITEMS]

resultset:

 PUBLICATION_ID    ITEM_REFERENCE_ID
      24                      701
      24                      702

I want to show the above result set in to single row like below:

 PUBLICATION_ID    ITEM_REFERENCE_ID1    ITEM_REFERENCE_ID2
     24                      701                 702.

so i tried pivot query like below but getting null values.

 /****** Script for SelectTopNRows command from SSMS  ******/
 select [PUBLICATION_ID] ,[0],[1]  from 
 (SELECT TOP 2
       [PUBLICATION_ID],[ITEM_REFERENCE_ID]       
   FROM [Tridion_Broker].[dbo].[ITEMS])s     
   pivot
  (
 avg(ITEM_REFERENCE_ID)
  FOR 
 ITEM_REFERENCE_ID IN ([0],[1] )  
  )AS pvt

 
more ▼

asked May 30, 2013 at 09:29 AM in Default

avatar image

samba
0 2 2 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

SELECT [PUBLICATION_ID] ,[701] AS ITEM_REFERENCE_ID1,[702] AS ITEM_REFERENCE_ID2 FROM

(SELECT [PUBLICATION_ID],[ITEM_REFERENCE_ID] FROM [Tridion_Broker].[dbo].[ITEMS]) SS

PIVOT ( MAX ([ITEM_REFERENCE_ID]) FOR [ITEM_REFERENCE_ID] IN ([701],[702]) ) AS PVT

more ▼

answered May 31, 2013 at 07:19 AM

avatar image

opusbabu
20 1

hello Opusbabu, thanks for your response.

I have tried your solution earlier itself.but i want to specify values dynamically.i mean the IN operator should take values(701,702) dynamically from the ITEMS table [ITEM_REFERENCE_ID] column.

Please suggest further..

May 31, 2013 at 09:09 AM samba
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x112

asked: May 30, 2013 at 09:29 AM

Seen: 1413 times

Last Updated: May 31, 2013 at 09:59 AM

Copyright 2017 Redgate Software. Privacy Policy