Rows based on Columns - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Rows based on Columns

Author

Topic

ravishwor
Starting Member

20 Posts

Posted - 2008-02-03 : 22:40:10

I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need.

I have a Table/View called [Shipment] with the data below.

ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA

I want the following as my result.

ShipNo Supplier1 Supplier2
==========================
1 CFA TFA
2 LRA LRB
3 AB
4 TFA

Thx.
Rav

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 23:27:49

[code]DECLARE @temp table
(ShipNo int,
Supplier varchar(10)
)

INSERT INTO @temp VALUES (1,'CFA')
INSERT INTO @temp VALUES (1, 'TFA')
INSERT INTO @temp VALUES (2, 'LRA')
INSERT INTO @temp VALUES (2, 'LRB')
INSERT INTO @temp VALUES (3, 'ABC')
INSERT INTO @temp VALUES (4, 'TFA')

;
WITH Your_CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ShipNo ORDER BY Supplier) AS RowNo,
ShipNo,
Supplier
FROM @temp
)

SELECT t1.ShipNo,t1.Supplier,ISNULL(t2.Supplier,'')
FROM Your_CTE t1
OUTER APPLy(SELECT Supplier
FROM Your_CTE
WHERE ShipNo=t1.ShipNo
AND RowNo=2) t2
WHERE t1.RowNo=1

output
---------------------------
ShipNo Supplier1 Supplier2
----------- ---------- ----------
1 CFA TFA
2 LRA LRB
3 ABC
4 TFA [/code]

ravishwor
Starting Member

20 Posts

Posted - 2008-02-03 : 23:43:43

Thx a lot visakh16,
can the Columns be made dynamic, ie,
ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA
1 ABC

ShipNo Supplier1 Supplier2 Supplier3
====================================
1 CFA TFA ABC
2 LRA LRB
3 AB
4 TFA

Thx a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 00:45:09

You need to use dynamic SQL to achieve this:-

CREATE table #temp 
(ShipNo int,
 Supplier varchar(10)
)

INSERT INTO #temp VALUES (1,'CFA')
INSERT INTO #temp VALUES (1, 'TFA')
INSERT INTO #temp VALUES (2, 'LRA')
INSERT INTO #temp VALUES (2, 'LRB')
INSERT INTO #temp VALUES (3, 'ABC')
INSERT INTO #temp VALUES (4, 'TFA')
INSERT INTO #temp VALUES (2, 'KKL')
INSERT INTO #temp VALUES (2, 'MMC')
INSERT INTO #temp VALUES (4, 'IIF')
INSERT INTO #temp VALUES (1, 'TTK')

DECLARE @ShipList varchar(2000)

SELECT @ShipList = LEFT(sl.ShipList,LEN(sl.ShipList)-1)
FROM (SELECT DISTINCT 'Supplier' + CAST(ROW_NUMBER() OVER (PARTITION BY ShipNo ORDER BY Supplier) AS varchar(3))  + ',' AS [text()]
      FROM #temp
      FOR XML PATH(''))sl(ShipList)

DECLARE @sql varchar(max)
SET @sql='
SELECT *
FROM
(SELECT ShipNo,
        Supplier,
        ''Supplier'' + CAST(ROW_NUMBER() OVER (PARTITION BY ShipNo ORDER BY Supplier) AS varchar(3)) AS SupplierNo
FROM #temp)d
PIVOT
(
MAX(d.Supplier)
FOR d.SupplierNo in (['+ REPLACE(@ShipList,',','],[') + ']))p' 
EXEC (@Sql)




output
-------------------------------
ShipNo      Supplier1  Supplier2  Supplier3  Supplier4
----------- ---------- ---------- ---------- ----------
1           CFA        TFA        TTK        NULL
2           KKL        LRA        LRB        MMC
3           ABC        NULL       NULL       NULL
4           IIF        TFA        NULL       NULL

ravishwor
Starting Member

20 Posts

Posted - 2008-02-05 : 00:20:12

Thx a lot visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-05 : 00:38:21

You are welcome ravishwor. Feel free to post whenever you have another doubt.

ravishwor
Starting Member

20 Posts

Posted - 2008-03-31 : 01:33:47

Hi Visakh16,

i get an error when i try to run this? can u pls help me? something to do with the quotes that i cannot figure out!

ERROR MSG:
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Supplier'.

DECLARE @SupplierList varchar(2000)

SELECT @SupplierList = LEFT(sl.SupplierList,LEN(sl.SupplierList)-1)
FROM (SELECT DISTINCT 'Supplier' + CAST(ROW_NUMBER() OVER (PARTITION BY stock_code ORDER BY cre_accountcode) AS varchar(20)) + ',' AS [text()]
FROM stock_supplier
FOR XML PATH(''))sl(SupplierList)

DECLARE @sql varchar(max)

SET @sql='
SELECT *
FROM
(SELECT Stock_code, cre_accountcode,
''Supplier'' + CAST(ROW_NUMBER() OVER (PARTITION BY Stock_code ORDER BY cre_accountcode) AS varchar(20)) AS SupplierNo
FROM stock_supplier) d
PIVOT
(
MAX(d.Supplier)
FOR d.SupplierNo in (['+ REPLACE(@SupplierList,',','],[') + ']))p'

EXEC (@Sql)

Thx,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources