January 17, 2011 at 8:38 am
I have a scenario in which the Primary name and the secondary names gets defined in columns for a Store in store table,
Now the same primary names and secondary names get stored in the second table in a Row by Row fashion. I have an occurence table in which a particular store participates and
and now i need to display the result set for the same store in Pivoted fashion; one beside the other with the primary
and the secondary names.
I was wondering how would I identify in the current result set that the Name displayed is a primary or a secondary.
i.e. to retrive the result set when i just need primary names of a group of storeid's I was thinking of using variants of ROW_NUMBER
but wasnt sure if that was a correct approach since i have nothing to identify the unique row.
CREATE TABLE #Store(StoreID INT NOT NULL,
PrimaryName VARCHAR(50) NOT NULL,
SecondaryName varchar (250) NOT NULL,
)
INSERT INTO #Store (StoreID,PrimaryName,SecondaryName)
VALUES (1,'A1','Engineering')
INSERT INTO #Store (StoreID,PrimaryName,SecondaryName)
VALUES (2,'A3','Sales')
CREATE TABLE #Text ( TextID INT, Names varchar (250),SToreID INT )
INSERT INTO #Text ( TextID,Names,StoreID)
SELECT NEWID (),'A1',1 UNION ALL
SELECT NEWID (),'Engineering',1 UNION ALL
SELECT NEWID (),'A3',2 UNION ALL
SELECT NEWID (),'Sales',2 UNION ALL
SELECT NEWID (),'A4',3 UNION ALL
SELECT NEWID (),'Marketing',3
CREATE TABLE #Occurence ( OcID INT,StoreID INT)
INSERT INTO #Occurence (OcID,StoreID)
SELECT 1,1 UNION ALL
SELECT 20,2
--DROP TABLE #Store
--DROP TABLE #TEXT
-- DROP TABLE #Occurence
SELECT * FROM #Occurence
SELECT * FROM #Store
SELECT * FROM #TEXT
SELECT o.SToreID,a.Names
FROM #Occurence o
INNER JOIN #Store s
ON o.StoreID = s.StoreID
OUTER APPLY ( SELECT *
FROM #TEXT t
WHERE t.SToreiD = s.storeiD
AND ( t.Names = s.PrimaryName
OR s.SecondaryName = t.names)
) a
--WHERE o.SToreID = 1
January 17, 2011 at 10:10 am
Was this so simple? It seems to do the work though
SELECT o.SToreID,a.Names, CASE WHEN a.Names = s.PrimaryName THEN 'Primary' ELSE 'Secondary' END AS Source
FROM #Occurence o
INNER JOIN #Store s
ON o.StoreID = s.StoreID
OUTER APPLY ( SELECT *
FROM #TEXT t
WHERE t.SToreiD = s.storeiD
AND ( t.Names = s.PrimaryName
OR s.SecondaryName = t.names)
) a
WHERE o.SToreID = 1
January 17, 2011 at 11:52 am
Thanks for your inputs Celko;
However I want to tell you that this is not even a correct depiction of our DB and is just a crude scratch display of what i wanted to accomplish by showing you the content in the data.You can ignore the way i have designed these table as of now, and just use the result set in these tables.
Currently i get the rows in a row by row fashion for E.g. In this case
A1
Engineering
with all the rest of columns being alike; I just wanted to know if those rows could be identified whether they have been coming from the PrimaryName or the SecondaryName so my result would be something in this fashion; which i achieved through putting a CASE statement. But still wondering if it was/is the right approach.
A1 Primary
Engineering Secondary
Sorry for the efforts caused to you for dissecting the design of the table.
January 17, 2011 at 1:40 pm
Thanks Celko, It did work, I modified/tweaked your solution according my needs and it seems to be giving the correct output and since the stores are not going to be in millions or thousands of rows, I can infact do an UNION ALL and use your method.
Thanks for your suggestions/inputs.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply