November 7, 2018 at 12:23 pm
It appears to be an uncommon task. I am looking for help on how to write this SQL Statement.
I am attaching an XL file with 2 spreadsheets:
'4 tables' and 'Desired Result'.
The '4 tables' spreadsheet shows 4 tables and data in them.
The 'Desired Result' spreadsheet shows a sample result set per one school (there will be same # of rows per each school in the result-set),
I am looking for help with how to write such query that will produce the DESIRED RESULT from the 4 non-joined and non-joinable tables.
There will be another column that I will use for some calculations for certain rows. the rows that will have no values in calculations still need to be there as empty placeholders. That is why a query is sought to produce the
DESIRED RESULT.
I appreciate your help with this.
It looks like it may be a complex query that I have no idea of how to approach.
Likes to play Chess
November 7, 2018 at 12:39 pm
;WITH A(A) AS (SELECT '' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C))
,B(N) AS (SELECT TOP(200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +1000 N FROM A A,A B)
SELECT N SchoolId
INTO #School
FROM B
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C Grade
INTO #Grade
FROM (VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12'),('KG'),('PK'),('PS')) T(C)
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C Sex
INTO #Sex
FROM (VALUES('M'),('F'),('A')) T(C)
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C RaceCode
INTO #RaceCode
FROM (VALUES('AMIND'),('ASIAN'),('BLACK'),('HI/PI'),('WHILE'),('MULTI')) T(C)
GO
/* ******************************* */
/* Query to get required results */
/* ******************************* */
SELECT s.SchoolId School, g.Grade, sx.Sex, r.RaceCode
FROM #School s
CROSS JOIN #Grade g
CROSS JOIN #Sex sx
CROSS JOIN #RaceCode r
ORDER BY 1,2,3,4
/* ******************************* */
GO
DROP TABLE #School
DROP TABLE #Grade
DROP TABLE #Sex
DROP TABLE #RaceCode
GO
November 7, 2018 at 12:41 pm
"Each possible combination" = cartesian product. Simple as that.
November 7, 2018 at 1:41 pm
Jonathan AC Roberts - Wednesday, November 7, 2018 12:39 PM;WITH A(A) AS (SELECT '' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C))
,B(N) AS (SELECT TOP(200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +1000 N FROM A A,A B)
SELECT N SchoolId
INTO #School
FROM B
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C Grade
INTO #Grade
FROM (VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12'),('KG'),('PK'),('PS')) T(C)
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C Sex
INTO #Sex
FROM (VALUES('M'),('F'),('A')) T(C)
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C RaceCode
INTO #RaceCode
FROM (VALUES('AMIND'),('ASIAN'),('BLACK'),('HI/PI'),('WHILE'),('MULTI')) T(C)
GO/* ******************************* */
/* Query to get required results */
/* ******************************* */
SELECT s.SchoolId School, g.Grade, sx.Sex, r.RaceCode
FROM #School s
CROSS JOIN #Grade g
CROSS JOIN #Sex sx
CROSS JOIN #RaceCode r
ORDER BY 1,2,3,4
/* ******************************* */GO
DROP TABLE #School
DROP TABLE #Grade
DROP TABLE #Sex
DROP TABLE #RaceCode
GO
THANK YOU for your help. Looks really cool.
However, the school(numbers) are not consequtive 1001, 1002, 1003... they are numbers anywhere from 1 to 1600 with gaps in numbers, not consecutive.
They come from "SELECT School from Schools" table ( = 190 distinct CHAR (4) type values of non-sequential numbers converted to char (4) with zero(s) padded to the left to make 4 char string).
Occasional school numbers can also be 'A335' or '014Q'.
Can your query be modified to use the resultset of "SELECT School from Schools" ?
Likes to play Chess
November 7, 2018 at 1:48 pm
VoldemarG - Wednesday, November 7, 2018 1:41 PMJonathan AC Roberts - Wednesday, November 7, 2018 12:39 PM;WITH A(A) AS (SELECT '' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C))
,B(N) AS (SELECT TOP(200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) +1000 N FROM A A,A B)
SELECT N SchoolId
INTO #School
FROM B
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C Grade
INTO #Grade
FROM (VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12'),('KG'),('PK'),('PS')) T(C)
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C Sex
INTO #Sex
FROM (VALUES('M'),('F'),('A')) T(C)
GO
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Id, T.C RaceCode
INTO #RaceCode
FROM (VALUES('AMIND'),('ASIAN'),('BLACK'),('HI/PI'),('WHILE'),('MULTI')) T(C)
GO/* ******************************* */
/* Query to get required results */
/* ******************************* */
SELECT s.SchoolId School, g.Grade, sx.Sex, r.RaceCode
FROM #School s
CROSS JOIN #Grade g
CROSS JOIN #Sex sx
CROSS JOIN #RaceCode r
ORDER BY 1,2,3,4
/* ******************************* */GO
DROP TABLE #School
DROP TABLE #Grade
DROP TABLE #Sex
DROP TABLE #RaceCode
GOTHANK YOU for your help. Looks really cool.
However, the school(numbers) are not consequtive 1001, 1002, 1003... they are numbers anywhere from 1 to 1600 with gaps in numbers, not consecutive.
They come from "SELECT School from Schools" table ( = 190 distinct CHAR (4) type values of non-sequential numbers converted to char (4) with zero(s) padded to the left to make 4 char string).
Occasional school numbers can also be 'A335' or '014Q'.
Can your query be modified to use the resultset of "SELECT School from Schools" ?
SELECT s.School, g.Grade, sx.Sex, r.RaceCode
FROM Schools s
CROSS JOIN #Grade g
CROSS JOIN #Sex sx
CROSS JOIN #RaceCode r
ORDER BY 1,2,3,4
November 9, 2018 at 8:47 am
Thanks.
Keeping your recommendations in mind, I ended up doing it this way:
Select S.*, race
FROM SchoolGradeSex S
CROSS JOIN
(SELECT 'AMIND' as race
UNION SELECT 'ASIAN' as Race UNION SELECT 'BLACK' as Race
UNION SELECT 'HI/PI' as Race UNION SELECT 'WHITE' as Race
) r
ORDER BY 1,2,3,4
Likes to play Chess
November 9, 2018 at 8:55 am
VoldemarG - Friday, November 9, 2018 8:47 AMThanks.
Keeping your recommendations in mind, I ended up doing it this way:Select S.*, race
FROM SchoolGradeSex S
CROSS JOIN
(SELECT 'AMIND' as race
UNION SELECT 'ASIAN' as Race UNION SELECT 'BLACK' as Race
UNION SELECT 'HI/PI' as Race UNION SELECT 'WHITE' as Race
) r
ORDER BY 1,2,3,4
Just my 2 cents on your code:
select
.* -- Specify the columns you need to return, even if you need to return all the columns
, [r].race
from
[dbo].[SchoolGradeSex] S -- Specify the schema of the table(s)
CROSS JOIN (select [race] from (values ('AMIND'),('ASIAN'),('BLACK'),('HI/PI'),('WHITE'))dt(race)) [r] -- Using SQL 2016, use TVC instead of union or union all
order by 1,2,3,4; -- Use the column names in your order by not the ordinal position
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply