SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert the result set from row wise to column wise


Convert the result set from row wise to column wise

Author
Message
s.kumaraguru
s.kumaraguru
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Hi Friends,

I want to Convert the result set from column wise to row wise. For Example, if i execute on select query i have the following output.

SupplierKey Value
----------- -----
BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 70
BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 80
BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 90


I just want to chage the result set like this.

SupplierKey Value1 value2 value3
----------- ------ ------ ------
BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28 70 80 90


can any one give some solution for this?

Regards,
Kumar
SQLMAIN
SQLMAIN
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 111
This is using a recent post on PIVOT SQL technique.


--Creating a table with your type of data
create table TT
(
SUPKEY varchar(100),
value tinyint
)

insert into TT values ('BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 70)
insert into TT values ('BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 80)
insert into TT values ('BF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 90)

insert into TT values ('AF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 70)
insert into TT values ('AF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 80)
insert into TT values ('AF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 90)

insert into TT values ('XF5EC5CA-C0D8-4402-AA1E-3ACEB6F45A28', 70)

--select * from tt

--Create another temp table with UR data and a KEY col
drop table #temp
SELECT SUPKEY, VALUE, CAST(0 AS INT) AS KEYVAL
INTO #temp
FROM tt

--Index - so we can put correct values for KEY coL
CREATE CLUSTERED INDEX Composite ON #temp (SUPKEY, VALUE)

DECLARE @PrevSUPKEY varchar(100)
SET @PrevSUPKEY = 0
DECLARE @keycnt INT


--Create running count in the KEY Column
UPDATE #temp
SET @keycnt = KEYVAL = CASE WHEN SUPKEY = @PrevSUPKEY THEN @keycnt+1 ELSE 1 END,
@PrevSUPKEY = SUPKEY
FROM #temp WITH (INDEX(Composite),TABLOCKX)

--Now create a dynamix SQL to produce the reqd output
DECLARE @SQL0 VARCHAR(8000)
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)

SELECT @SQL0 = 'SELECT SUPKEY,'

SELECT @SQL1 = ISNULL(@SQL1+',','')+CHAR(10)
+ 'MAX(CASE WHEN KEYVAL = ' + CAST(d.KEYVAL AS VARCHAR(10)) + ' '
+ 'THEN VALUE ELSE '''' END) AS VALUE' + CAST(d.KEYVAL AS VARCHAR(10))
FROM (SELECT DISTINCT KEYVAL FROM #temp) d
ORDER BY d.KEYVAL

SELECT @SQL2 = CHAR(10)+'FROM #temp GROUP BY SUPKEY'

--Execute the dynamic SQL
--PRINT @SQL0+@SQL1+@SQL2
exec (@SQL0+@SQL1+@SQL2)
SqlUser-529296
SqlUser-529296
SSChasing Mays
SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)SSChasing Mays (626 reputation)

Group: General Forum Members
Points: 626 Visits: 754
thanks a lot ....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search