Query to show table Products with its modes

Question

Post reply

Query to show table Products with its modes

sistemas_casinomnes

SSChasing Mays

Points: 659
More actions
November 28, 2014 at 4:57 am

#295504

Hello, I need assistance with a query that show me a Products Tables and its models, 1 product could have 2 or more models
Example Table Products
id Name
1 Product 1
2 Product 2
3 Product 3
................
Example Table Models
idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1
.......................................
And I want to show:
Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1
How can I query to show that?
Thanks
Viky

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

You may build XML structured as requiered. Find samples here

http://www.databasejournal.com/features/mssql/article.php/3885251/Creating-XML-in-SQL-Server.htm

Post DDL and sample data if more assistance is needed.

aaron.reese SSChampion Points: 13498 More actions · Answer 2

This looks like a presentation requirement rather than a data requirement.

You could pull the whole of the table into Excel and use Pivot tables.

If you need to show details about each level (e.g. product name, price, description) then underneath details of the model (colour,size, etc) then a report in SSRS would be most appropriate but you would still pull a single dataset with the product data repeated for each model row. The presentation could be done just as easily in Crystal Reports, Access Reports, QlikView, Tableau or any number of other reporting packages.

As already stated XML would be a generic solution for this data if you need to make it portable.

Aaron

stook SSC Enthusiast Points: 126 More actions · Answer 3

sistemas_casinomnes (11/28/2014)
Hello, I need assistance with a query that show me a Products Tables and its models, 1 product could have 2 or more models

its slow with sql - use the front end, but it can be done smth like this

declare @products table (id int identity primary key, name nvarchar(100))

declare @models table (idmodel int identity primary key, idproduct int, model nvarchar(100))

insert into @products (name) values ('Product 1'),('Product 2'),('Product 3')

insert into @models (idproduct,model) values (1,'Model 1'),(1,'Model 2'),(1,'Model 3'),(2,'Model 1'),(2,'Model 2'),(3,'Model 1')

;with result as (

select id,name, model, row_number() over (order by id,idmodel) rn from @products inner join @models on id=idproduct

)

select case when id=(select id from result b where b.rn=a.rn-1) then null else name end as product, model from result a

Jeff Moden SSC Guru Points: 1003911 More actions · Answer 4

It won't be slow if you avoid the self join of the CTE, which causes it to go through both tables twice. This will work almost as fast as if you didn't do any formatting.

WITH

cteEnumerate AS

(

SELECT p.Name

,m.Model

,RowNum = ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY m.Model)

FROM @Products p

JOIN @models m ON p.ID = m.IDProduct

)

SELECT Product = CASE WHEN RowNum > 1 THEN '' ELSE Name END,

Model

FROM cteEnumerate

;

--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)

sistemas_casinomnes SSChasing Mays Points: 659 More actions · Answer 5

sistemas_casinomnes

SSChasing Mays

Points: 659

December 1, 2014 at 5:44 am

#1761669

Thanks a lot for your help!!!