SQL Server 2008中基于双数据源创建临时表或CTE的方法

Let's break down how to build the required temporary table or CTE for your report, which needs to pair every employee with each selected course (resulting in one row per employee-course combination).

Key Concept #

The core here is using a cross join between the Person table (all employees) and your selected courses CTE/temporary table. This will generate the exact one-to-many mapping you need—each employee gets a row for every selected course.

1. Using CTEs (Recommended for Ad-Hoc Queries) #

First, we'll define a CTE to hold the selected courses. In a real report scenario, this would pull values from your dropdown parameter—for example, if using SSRS, you can pass the selected courses as a dataset or table-valued parameter. Here's a sample implementation:

-- Define the CTE for selected courses (replace with your actual parameter logic)
WITH SelectedCourses AS (
    SELECT 'Course A' AS CourseName
    UNION ALL
    SELECT 'Course B' AS CourseName
    UNION ALL
    SELECT 'Course C' AS CourseName
),
EmployeeCoursePairs AS (
    -- Cross join to get every employee paired with every selected course
    SELECT 
        p.EmployeeID,
        sc.CourseName
    FROM Person p
    CROSS JOIN SelectedCourses sc
)
-- Use the CTE in your report query (e.g., select all records)
SELECT * FROM EmployeeCoursePairs;

2. Using a Temporary Table (Good for Reusable Logic) #

If you prefer a temporary table instead of a CTE (maybe for multiple downstream uses), here's how to set it up:

-- Create a temp table to hold selected courses
CREATE TABLE #SelectedCourses (CourseName VARCHAR(100));

-- Insert the selected courses (again, replace with your parameter input)
INSERT INTO #SelectedCourses (CourseName)
VALUES ('Course A'), ('Course B'), ('Course C');

-- Create the employee-course pairs temp table
CREATE TABLE #EmployeeCoursePairs (
    EmployeeID INT,
    CourseName VARCHAR(100)
);

-- Populate the pairs using cross join
INSERT INTO #EmployeeCoursePairs (EmployeeID, CourseName)
SELECT 
    p.EmployeeID,
    sc.CourseName
FROM Person p
CROSS JOIN #SelectedCourses sc;

-- Use the temp table in your report
SELECT * FROM #EmployeeCoursePairs;

-- Clean up temp tables when done (optional in SQL Server, but good practice)
DROP TABLE #SelectedCourses;
DROP TABLE #EmployeeCoursePairs;

Handling Report Parameters in SQL Server 2008 #

Since SQL Server 2008 supports table-valued parameters (TVPs), this is the most efficient way to pass selected dropdown values from your report tool (like SSRS):

1. First, create a user-defined table type:

CREATE TYPE CourseListType AS TABLE (CourseName VARCHAR(100));

2. Then, use it in your query or stored procedure:

DECLARE @SelectedCourses CourseListType;

-- Insert values from your dropdown (report tool would handle this)
INSERT INTO @SelectedCourses (CourseName)
VALUES ('Course A'), ('Course B'), ('Course C');

-- Generate the pairs
SELECT 
    p.EmployeeID,
    sc.CourseName
FROM Person p
CROSS JOIN @SelectedCourses sc;

This approach avoids messy string splitting (since SQL Server 2008 doesn't have STRING_SPLIT built-in) and is more performant for larger datasets.

内容的提问来源于stack exchange，提问作者dantealy