Reversing cross join input

A table is populated by the following stored procedure:

exec('
insert into tblSegments
    (SegmentName, CarTypeID, EngineTypeID, AxleTypeID)
select distinct
    ''' + @SegmentName + '''
    , CT.CarTypeID
    , ET.EngineTypeID
    , AT.AxleTypeID
from
    tblCarTypes CT
        cross join tblEngineTypes ET
        cross join tblAxleTypes AT
where
    CT.CarTypeName in (' + @CarTypes + ')
    and ET.EngineTypeName in (' + @EngineTypes + ')
    and AT.AxleTypeName in (' + @AxleTypes + ')
')

parameters, with the exception of @SegmentName, are strings such as (for @CarTypes) 'hatchback','suv','sedan'.

Can the data in the table be used to create a list, for a single SegmentName, of the previous entries to the stored procedure akin to

Run1: @CarTypes, @EngineTypes, @AxleTypes Run2: @CarTypes, @EngineTypes, @AxleTypes Run3: @CarTypes, @EngineTypes, @AxleTypes

...?

Runs don't need to be in sequential order. The process can involve a combination of T-SQL and C#. I'm pretty sure this is impossible; perhaps someone can prove me wrong.

Answers


No, it's not possible because you're taking in a potentially comma-delimited string of values which will create separate rows in your result table. You can easily get a single value each for the CarTypes, EngineTypes and AxleTypes variables, but to group them separately by each execution of your dynamic SQL you would need some kind of executionID column or something to group the rows on per execution.

So you're correct in that what you want to do is completely possible, but not with the schema design you've provided. I would just create another table and populate it at runtime if this is information you want to keep. You could put an identify column on the table that houses the input variables and use the @@IDENTITY for the insert into that table to populate an executionID column in your main table so you can easily associate the variable summary table with the cross joined result table.


Need Your Help

object oriented javascript - inheritance implementation

javascript javascript-objects

I have two module with very similar methods (it is a part of an adapter pattern that implements the same methods/interface). The differences are in two lines of code:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.