Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert the result set from row wise to column wise Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2007 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 24, 2007 10:33 PM
Points: 1, 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
Post #414396
Posted Wednesday, October 24, 2007 9:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:14 AM
Points: 44, 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)
Post #414508
Posted Sunday, October 28, 2007 10:09 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 1:56 AM
Points: 438, Visits: 754
thanks a lot ....

Post #415825
Posted Sunday, October 28, 2007 11:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Sunday, September 7, 2014 11:11 PM
Points: 2,622, Visits: 328
Try this....

msdn2.microsoft.com/en-us/library/aa216173(sql.80).aspx
Post #415832
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse