Need assistance with converting [0..n] to [1..1] for a view
-
The following image should make things clear:
The view in question is created via a LEFT OUTER JOIN. I know how to get at the second column in the output view, but the third column eludes me. As you can see, I want a bunch of values for a single 'Class' to be presented as comma-separated values (with each value in enclosed in single-quotes).
DDL
--===== If the test tables already exist, drop themIF OBJECT_ID('TempDB..#TABLE1', 'U') IS NOT NULL
BEGIN
DROP TABLE #TABLE1;
END;
IF OBJECT_ID('TempDB..#TABLE2', 'U') IS NOT NULL
BEGIN
DROP TABLE #TABLE2;
END;
IF OBJECT_ID('TempDB..#OUTPUTVIEW', 'U') IS NOT NULL
BEGIN
DROP TABLE #OUTPUTVIEW;
END;
--===== Create the temporary test table #TABLE1 with
CREATE TABLE #TABLE1 (
PK INT PRIMARY KEY,
Class NVARCHAR(2)
);
--===== Create the temporary test table #TABLE2 with
CREATE TABLE #TABLE2 (
PK INT PRIMARY KEY,
FK INT REFERENCES #TABLE1(PK),
Names NVARCHAR(8)
);
--===== Create the temporary test table #OUTPUTVIEW with
CREATE TABLE #OUTPUTVIEW (
Class NVARCHAR(2),
NameCount INT,
Names NVARCHAR(512)
);
DML
INSERT INTO #TABLE1 (PK, Class)SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D' UNION ALL
SELECT 5, 'E';
INSERT INTO #TABLE2 (PK, FK, Names)
SELECT 1001, 1, 'Mike' UNION ALL
SELECT 1002, 1, '' UNION ALL
SELECT 1003, 2, 'Sally' UNION ALL
SELECT 1004, 4, 'Jim' UNION ALL
SELECT 1005, 4, 'Herb' UNION ALL
SELECT 1006, 5, '';
INSERT INTO #OUTPUTVIEW (Class, NameCount, Names)
SELECT 'A', 1, CHAR(39) + 'Mike' + CHAR(39) + ', ' + CHAR(39) + CHAR(39) UNION ALL
SELECT 'B', 1, CHAR(39) + 'Sally' + CHAR(39) UNION ALL
SELECT 'C', 0, '' UNION ALL
SELECT 'D', 2, CHAR(39) + 'Jim' + CHAR(39) + ', ' + CHAR(39) + 'Herb' + CHAR(39) UNION ALL
SELECT 'E', 0, CHAR(39) + CHAR(39);
Can someone assist me on how to go about this?
-
EXCEPTIONAL REPRESENTATION OF THE PROBLEM!
Hats off 🙂
The code is on ur way!
-
; WITH BaseTable AS(
SELECT T1.Class
,Names = CASE WHEN T2.Names = '' THEN '''''' ELSE T2.Names END
,T2.PK
,CT = COUNT(CASE WHEN T2.Names = '' THEN NULL ELSE T2.Names END) OVER (PARTITION BY T1.Class)
FROM #TABLE1 T1
LEFT JOIN #TABLE2 T2
ON T1.PK = T2.FK
)
SELECT BT.Class
,BT.CT
,Names =
ISNULL(STUFF(
(SELECT ','+Inr.Names
FROM BaseTable Inr
WHERE inr.Class = BT.Class
ORDER BY Inr.PK
FOR XML PATH(''),TYPE
).value('.','VARCHAR(MAX)')
, 1,1,SPACE(0)
)
, '')
FROM BaseTable BT
GROUP BY BT.Class , BT.CT
-
CELKO (7/17/2012)
Please stop drawing pictures coloring code. All we really need is the DDL, sample data and specs. It is a bitch to have to edit all that confetti to get to it.Why do you say so? Isn't a picture worth a thousand words ?
-
CELKO (7/17/2012)
Please stop drawing pictures coloring code. All we really need is the DDL, sample data and specs. It is a bitch to have to edit all that confetti to get to it.But the real issue is why do you wish to destroy First Normal Form? Display formatting is done is the presentation layer, NEVER in the database. This is freshman Software Engineering, not RDBMS. You will wind up with a mess that has XML or CLR code in the SQL to screw up performance, portability and maintainability.
You don't have to edit a bloody thing. The op posted the DDL and the sample data just the way we need it. The picture helps, too.
And he's not destroying First Normal Form. He's making a view to report from.
--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) -
This is a little simpler but on the same lines as ColCoffee:
--===== Create the temporary test table #TABLE1 with
CREATE TABLE TABLE1 (
PK INT PRIMARY KEY,
Class NVARCHAR(2)
);
--===== Create the temporary test table #TABLE2 with
CREATE TABLE TABLE2 (
PK INT PRIMARY KEY,
FK INT REFERENCES TABLE1(PK),
Names NVARCHAR(8)
);
--===== Create the temporary test table #OUTPUTVIEW with
CREATE TABLE OUTPUTVIEW (
Class NVARCHAR(2),
NameCount INT,
Names NVARCHAR(512)
);
--Inserting Sample Data
INSERT INTO TABLE1 (PK, Class)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D' UNION ALL
SELECT 5, 'E';
INSERT INTO TABLE2 (PK, FK, Names)
SELECT 1001, 1, 'Mike' UNION ALL
SELECT 1002, 1, '' UNION ALL
SELECT 1003, 2, 'Sally' UNION ALL
SELECT 1004, 4, 'Jim' UNION ALL
SELECT 1005, 4, 'Herb' UNION ALL
SELECT 1006, 5, '';
INSERT INTO OUTPUTVIEW (Class, NameCount, Names)
SELECT 'A', 1, CHAR(39) + 'Mike' + CHAR(39) + ', ' + CHAR(39) + CHAR(39) UNION ALL
SELECT 'B', 1, CHAR(39) + 'Sally' + CHAR(39) UNION ALL
SELECT 'C', 0, '' UNION ALL
SELECT 'D', 2, CHAR(39) + 'Jim' + CHAR(39) + ', ' + CHAR(39) + 'Herb' + CHAR(39) UNION ALL
--Query
Select b.Class,
SUM(Case When c.Names = '' OR c.Names IS NULL Then 0 Else 1 End) As NameCount,
IsNULL(STUFF((Select ',''' + a.Names + '''' From Table2 As a Where a.FK = b.PK For XML Path('')), 1 , 1, ''), '') As Names
From TABLE1 As b Left JOIN TABLE2 As c On b.PK = c.FK
Group By b.Class, b.PK
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply