Multiple and flexible columns in query.

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 2000 Forums

Transact-SQL (2000)

Multiple and flexible columns in query.

Author

Topic

scozzese
Starting Member

12 Posts

Posted - 2004-02-06 : 09:28:48

I have the following table "MyTable" with records:
Field.Code Field.Q_ty Field.Cell
A 10 1
A 12 2
B 15 1
B 9 2
B 13 3

Now I need help to create a query with multiple columns for Field.Cell.
The number of column Field.Q_ty_Cell_? must be the same of the record that result from:
SELECT DISTINCT Field.Cell FROM MyTaBLE
At the end I'll expect the following result:

Field.Code Field.Q_ty_Cell_1 Field.Q_ty_Cell_2 Field.Q_ty_Cell_3
A 10 12 0
B 15 9 13

Thankyou for help.
Roberto

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-02-06 : 09:44:13

You want a pivot table then?
These 2 articles should get you started

[url]http://www.sqlteam.com/item.asp?ItemID=2955[/url]

[url]http://www.sqlteam.com/item.asp?ItemID=5741[/url]

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-07 : 14:33:56

This should work if 'MyTable' isn't too large

--*******************************************************************

DECLARE @Code VarChar(1)
DECLARE @Qty INT
DECLARE @Cell INT
DECLARE @MaxCols INT
DECLARE @CreateTableSQL VARCHAR(1000)
DECLARE @InsertTableSQL VARCHAR(1000)
DECLARE @UpdateSQL VARCHAR(1000)
DECLARE @SelectSQL VARCHAR(1000)
DECLARE @ColCT INT

SET @MaxCols = (SELECT MAX(ColCT)
FROM (SELECT Code, COUNT(Qty) ColCT
FROM MyTable
GROUP BY Code) as A)

SET @CreateTableSQL = 'CREATE TABLE #Results(Code VARCHAR(1)'
SET @InsertTableSQL = 'INSERT INTO #Results SELECT DISTINCT Code'
SET @ColCT = 1
WHILE @ColCT <= @MaxCols
BEGIN
SET @CreateTableSQL = @CreateTableSQL + ', Qty_' + RIGHT('000' + RTRIM(LTRIM(STR(@ColCT, 3))), 3) + ' Int'
SET @InsertTableSQL = @InsertTableSQL + ', 0'
SET @ColCT = @ColCT + 1
END

SET @CreateTableSQL = @CreateTableSQL + ') '
SET @InsertTableSQL = @InsertTableSQL + ' FROM MyTable '
SET @UpdateSQL = ''
DECLARE MyCur
CURSOR FOR
SELECT Code, Qty, Cell
FROM MyTable
ORDER BY Code, Qty, Cell

OPEN MyCur
FETCH NEXT FROM MyCur
INTO @Code, @Qty, @Cell

WHILE @@FETCH_STATUS = 0
BEGIN
SET @UpdateSQL = @UpdateSQL + ' UPDATE #Results SET ' + ' QTY_' + RIGHT('000' + RTRIM(LTRIM(STR(@Cell, 3))), 3) + ' = ' + STR(@Qty, 3) + ' WHERE Code = ''' + @Code + '''; '
FETCH NEXT FROM MyCur
INTO @Code, @Qty, @Cell
END

CLOSE MyCur
DEALLOCATE MyCur

EXEC(@CreateTableSQL + @InsertTableSQL + @UpdateSQL + ' SELECT * FROM #Results' )

--*******************************************************************

I'm sure that some of the other yakkers have got better ways to do this though

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources