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 2008 Forums
 Transact-SQL (2008)
 Inserting rows and columns based on column values

Author  Topic 

vkap
Starting Member

3 Posts

Posted - 2012-06-20 : 17:48:01
I have a tble with following values :
col1 col2 col3
aaa rack1 1-3
bbb rack2 22-23
ccc rack3 19-20

Output should be
col1 col2 col3 position
aaa rack1 1-3 1
aaa rack1 1-3 2
aaa rack1 1-3 3
bbb rack2 22-23 22
bbb rack2 22-23 23
ccc rack3 19-20 19
ccc rack3 19-20 20

Basically, i need a new column 'Position' and creating a row for each value from col3.

Can i do this on-fly and create a temp table? or i have to create a procedure?

thanks!!!
VK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-20 : 18:04:31
You could use a Number or Tally table. Here is one way to do it with an inline-tally table:
DECLARE @T TABLE (col1 varchar(3), col2 varchar(10), col3 varchar(10))
INSERT @T VALUES
('aaa', 'rack1', '1-3'),
('bbb', 'rack2', '22-23'),
('ccc', 'rack3', '19-20')


;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT
A.*,
T.N As Position
FROM
@T AS A
INNER JOIN
Tally AS T
ON T.N BETWEEN CAST(PARSENAME(REPLACE(col3, '-', '.'), 2) AS INT) AND CAST(PARSENAME(REPLACE(col3, '-', '.'), 1) AS INT)
Go to Top of Page

vkap
Starting Member

3 Posts

Posted - 2012-06-21 : 12:51:01
thanks Lamprey, it works really well!!! very nice suggestion!!
Go to Top of Page
   

- Advertisement -