Returning multiple rows from single row
- Does anyone know if it is possible to write a tsql select query that will return multiple rows from a single row.For example, you have a table with 2 fields (product and quantity).You have a row in this table for product='widget' and quantity=5I want a query that will return the following result:widget 1 5widget 2 5widget 3 5widget 4 5widget 5 5In other words - return a number of rows based on the value in the quantity column.Any ideas?CheersJohn
-
Create Table #Tmp(Product VarChar(50),Quantity Decimal(10,4))
Go
Insert #Tmp
Select 'Widget',5
Insert #Tmp
Select 'Widget',6
Insert #Tmp
Select 'Widget',4
GO
Select * from #Tmp
Go
Select Product,Counter,Quantity
From #Tmp as t
Cross Join (Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5) as c(Counter)
Where t.quantity=5
Go
Drop Table #Tmp
Go
-
With a minor variation:
Create Table #Tmp(Product VarChar(50),Quantity Decimal(10,4))
Go
Insert #Tmp
Select 'Widget',5
Insert #Tmp
Select 'Widget',6
Insert #Tmp
Select 'Widget',4
GO
Select * from #Tmp
Go
Select Product,number,Quantity
From #Tmp as t
Cross Join (SELECT number from master..spt_values where type ='P' ) N
Where N.number Between 1 and t.Quantity
order by Product,Quantity, number
Go
Drop Table #Tmp
Go
* Noel - Thanks for your quick responses.I had imagined the answer to be something like you suggest but I was hoping to avoid temporary tables.I would like to do this in a view if possible becuase it is going to be used as the data source to a report designer.I do not really want to dynamically create a view either.After spending quite a bit of time on this I decided it was not possible but I thought I would post anyway.Perhaps someone is up to the challenge!Thanks anywayJohn
-
carmines,
none of the methods shown require temporary tables they were created using temptables just to avoid the creation of a "true" table in our enviroment.
If you replace the #T1 on my case for your Base table you are going to get the results you wanted:
CREATE VIEW vwMyView AS
Select Top 100 Percent Product,number,Quantity
From YOURTABLE as t
Cross Join (SELECT number from master..spt_values where type ='P' ) N
Where N.number Between 1 and t.Quantity
order by Product,Quantity, number
Select * from vwMyView where Product ='x' and quantity =y
* Noel -
>I would like to do this in a view if possible becuase it is going to be used as the data source to a report designer.
How would you pass a parameter to a VIEW to get only "WIDGETS" to return? Would a FUNCTION be acceptable?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Thanks noeld,That will teach me not to read the answer thoroughly.Your reply works great - as you said it would.I am now very interested in spt_values - what is it?I can see it is a table on the master database - mine has rows for numbers up to 256 where type='P' - is this always the case?is it safe to use this table? (I have read some comments about not directly using system tables).I cannot find any detailed info in BOL so any details would be greatly appreciated.Thanks againJohn
-
As noeld uses spt_values in Master, I do also for the same kind of usages. This table seems to perform even faster than any "user table" made with the same values and indexing. It works great when CROSS JOINing to get iterations. I use it for values, not only between 0 and 255, but even performs great when CROSS JOINed with itself for values 0 - 65535. Its a quick and easy way to get a result set of integers without gaps. While it is a table in Master, and MS has the right to change it, I'm going to use it. If/when it does not work for this type of usage any longer, I'll mass search & replace to use a home made user table. Until then, I want to reap the performance and convenience benifits of using this table and the data it furnishes to me.
Just my $.02
Once you understand the BITs, all the pieces come together
-
All well and good...
But the "poster" said that he wanted to do this as a VIEW!
I asked the question a while back, "How would he parametrize a VIEW" to accept the part name? Especially without using dynamic SQL? 
Enquiring minds what to know!--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
It is not a case of parameterising the view - I would simply use a where clause when selecting from the view.
Select * from vwMyView where Product ='widget'
Regards
John
-
carmines,
ThomasH has explained very well the use of that handy table which by the way has been around since the sybase times. I agree with him and if you have any doubts just create a "numbers" table with sequecial numbers and that's it.
* Noel -
Thanks carimines...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply