在 sql 中将数据表与平面数据表联系起来

Question

Table Fan
-----------------------
| FanId | Name | Info |
-----------------------
| 17111 | Fan1 | Info1|
-----------------------  
| 17112 | Fan2 | Info2|
-----------------------
1:m
Table ProfileFan
----------------------------------
| Id | LinkedInProfileId | FanId |
----------------------------------
| 1111    | 1          | 17111   |
----------------------------------
| 1112    | 2          | 17111   |
----------------------------------
| 1113    | 1          | 17112   |
----------------------------------
| 1114    | 2          | 17112   |
----------------------------------
m:1
Table LinkedInProfile
--------------------------
| LiId | Name | Client    |
--------------------------
| 1    | Linked1 | Client1|
--------------------------
| 2    | Linked2 | Client1 |
--------------------------

我需要的是将这些关系表中的数据导出到 CSV。所以我正在对作业进行一个程序，并安排它每小时运行一次。

问题是我需要在一行中插入一个风扇的查询数据。

ExportToCsv Table
--------------------------------------------------------------------
| FanName | FanInfo  | LinkedInProfile1| LinkedInProfile2 | Client |
--------------------------------------------------------------------
| Fan1    | FanInfo1 | Linked1(1111)   |  Linked2(1112)  | Client1 |
--------------------------------------------------------------------
| Fan2    | FanInfo2 | Linked1(1113)   |  Linked2(1114)  | Client1 |
--------------------------------------------------------------------

到目前为止，我的查询为每个ProfileFanId生成行。

BEGIN
DECLARE @ProfileFanId INT
SET @ProfileFanId=0
SELECT TOP (1)  @ProfileFanId =[dbo].[ExportCSV].ProfileFanId
    FROM [dbo].[ExportCSV] Order by ProfileFanId desc
IF @StatementType = 'Insert' 
INSERT INTO ExportCSV (FanId, SNUrl, FullName, FirstName, LastName, Company, JobTitle, [ExperienceDescription], [FANProfileLocation], [CompanyDescription], [CompanyIndustry], [CompanyLocationCity],
[CompanyLocationCountry], [NumberOfEmployees], [CompanySpecialities], [CompanyLiURL], [CompanyDomain], [TwitterHandles], [EMailAddress] ,[TelephoneNumber], ClientName1, CreatedAt, ProfileFanId )

SELECT DISTINCT B.LinkedInProfile ,B.SNUrl ,B.FullName, B.FirstName, B.LastName,B.Organization1,B.JobTitle,B.ExperienceDescr,B.ProfileLocation,B.OrganizationDescription1,B.Organization1Industry,
B.OrganizationCity1,B.OrganizationCountry1,B.Organization1Size, B.OrganizationSpecialities1, B.OrganizationLIID1,B.OrganizationDomain1, B.Twitter, B.Email, B.Phone, C.Name, SYSDATETIME(), A.Id, B.Id --,c.EmailResponseStatusId,c.FanStatusId,c.BatchId, n.DeliveryActionId, n.LoggedAt, f.ActivityId,f.Created
FROM ProfileFan A 
INNER JOIN Fan B ON A.FanId = B.Id
INNER JOIN LinkedInProfile C ON A.ProfileId = C.Id
--INNER JOIN Batch D ON A.BatchId = D.Id
where A.Id>@ProfileFanId
--------------------------------------------------------------------
| FanName | FanInfo  | LinkedInProfile1| LinkedInProfile2 | Client |
--------------------------------------------------------------------
| Fan1    | FanInfo1 | Linked1(1111)   |                  | Client1|
--------------------------------------------------------------------
| Fan1    | FanInfo1 | Linked2(1112)   |                  | Client1|
--------------------------------------------------------------------
| Fan2    | FanInfo2 | Linked1(1113)   |                  | Client1|
--------------------------------------------------------------------
| Fan2    | FanInfo2 | Linked2(1114)   |                  | Client1|
--------------------------------------------------------------------

欢迎任何有关如何获得所需输出的想法。提前感谢！

Answer 1

您需要在此处将 PIVOT 与动态列一起使用。

看看这个熟悉动态枢轴：https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

我在以下查询中模拟了您的示例：

CREATE TABLE Fan(FanId int, Name varchar(100), Info varchar(100));
INSERT INTO Fan VALUES (17111,'Fan1','Info1'),(17112,'Fan2','Info2');
CREATE TABLE LinkedInProfile(LiId int, Name varchar(100), Client varchar(100));
INSERT INTO LinkedInProfile VALUES (1,'Linked1','Client1'),(2,'Linked2','Client1');
CREATE TABLE ProfileFan(Id int, LinkedInProfileId int, FanId int);
INSERT INTO ProfileFan VALUES (1111,1,17111),(1112,2,17111),(1113,1,17112);
DECLARE @columns NVARCHAR(MAX), @columns1 NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @columns = STUFF((
            SELECT DISTINCT ',' + 'LinkedInProfile' + CAST(LinkedInProfileId AS VARCHAR) FROM ProfileFan
            FOR XML PATH('')
            ), 1, 1, '')
FROM ProfileFan;
SELECT @columns1 = STUFF((
            SELECT DISTINCT ',' + '[LinkedInProfile' + CAST(LinkedInProfileId AS VARCHAR) + ']' FROM ProfileFan
            FOR XML PATH('')
            ), 1, 1, '')
FROM ProfileFan;

SET @sql = 'SELECT Name, Info,' + @columns + ' FROM
(
  SELECT CONCAT(lip.Name,''('',pf.Id,'')'') AS val, f.Name as ''Name'', f.Info as ''Info'' , CONCAT(''LinkedInProfile'',pf.LinkedInProfileId) as LipId 
  FROM Fan f
  LEFT JOIN
  ProfileFan pf
  ON
  f.FanId = pf.FanId
  LEFT JOIN
  LinkedInProfile lip
  ON
  pf.LinkedInProfileId = lip.LiId
) AS src
PIVOT
(
  MAX(src.val) FOR src.LipId IN ('+ @columns1
  + ')
) AS p;';
EXEC sp_executesql @sql;

此动态查询@sql将给出以下输出：

SELECT Name, Info, LinkedInProfile1, LinkedInProfile2 FROM
(
  SELECT CONCAT(lip.Name,'(',pf.Id,')') AS val, f.Name as 'Name', f.Info as 'Info', CONCAT('LinkedInProfile',pf.LinkedInProfileId) as LipId 
  FROM Fan f
  LEFT JOIN
  ProfileFan pf
  ON
  f.FanId = pf.FanId
  LEFT JOIN
  LinkedInProfile lip
  ON
  pf.LinkedInProfileId = lip.LiId
) AS src
PIVOT
(
  MAX(src.val) FOR src.LipId IN ([LinkedInProfile1],[LinkedInProfile2])
) AS p;

结果集：

    Name    Info    LinkedInProfile1    LinkedInProfile2
1   Fan1    Info1   Linked1(1111)   Linked2(1112)
2   Fan2    Info2   Linked1(1113)   NULL