sql - 将结果拉成行，轻松转换/翻译成列？

Question

我接到了一项任务，要求我使用 SSIS 2008 包创建 CSV，然后通过 FTP 传输该 CSV，following this tutorial here .

我的查询还没有准备好。

我正在提取结果，其中我们要处理的列以行的形式出现。一直在阅读有关如何使用游标 + 存储过程执行此操作的信息，但想知道是否有人使用更简单的解决方案克服了这个障碍？

查询

---

SELECT 
    b.name as 'field',
    a.value
FROM 
    [PROD_21C_Sitecore_Web].[dbo].[VersionedFields] a
    INNER JOIN [PROD_21C_Sitecore_Web].[dbo].[Items] b
        on a.FieldId = b.ID
WHERE 
    ItemId = '8C1D5767-FB1A-47A6-913C-E78AAC24ABC9'

结果

---

field                      value
-------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UnderGradYear              1972
Position1                  <p>Cancer Centers of North Carolina, Asheville Hematology Oncology Associates - Medical Oncologist</p>
FellowshipCity1            Winston Salem, NC
FellowshipYear1            1981
__Updated by               sitecore\cvance
Position3                  <p>St. Joseph&#39;s Hospital - Subsection Chief of Hematology/ Oncology</p>
Languages                  {A5AB043B-86D3-4033-BEDD-928BCA0A13C3}
UnderGradCity              Chapel Hill, NC
Interests                  <p></p>
Locations                  {6127E1AE-E2BF-4517-8BCB-46590AEB06A8}|{A7D2EA25-1B3F-48AE-B347-FC1E06F48202}
__Revision                 22068c83-3504-4d89-a9bf-2a9e83a49ca3
LastName                   Paschal
FirstName                  Barton R.
MedicalSchoolCity          Atlanta, GA
Fellowship1                <p>Bowman Gray School of Medicine - Medical Oncology</p>
FullName                   Barton R. paschal
UnderGradSchool            University of North Carolina
Residency                  <p>Louisiana State University Medical Center - Internal Medicine</p>
ResidencyYear              1979
ResidencyCity              Shreveport, LA
Specialities               {B5B0F45A-E2BE-4F3F-92A6-C39C0D3016C5}|{4FCCE0BC-F3D4-4E27-AA7F-D436E61B2A1B}
AwardsHonors               <p>Fellow, American College of Physicians, Phi Beta Kappa</p>
Position1City              Asheville, NC
Position2City              Asheville, NC
Title                      MD
__Created                  20120509T085416
Internship                 <p>LSU Medical Center</p>
Photo                      <image mediaid="{C0F47AFC-CBAE-4043-A52F-BF8E344DC6DA}" mediapath="/Images/Physicians/paschal-web" src="~/media/Images/Physicians/paschal-web.jpg" alt="paschal" height="" width="" hspace="" vspace="" />
Position2                  <p>Memorial Mission Hospital - Medical Oncologist &amp; Chair, Department of Internal Medicine</p>
BoardCertifications        Board Certified - Internal Medicine, Medical Oncology
Position3City              Asheville, NC
__Updated                  20130514T080506:635041155069332802
MedicalSchool              Emory University School of Medicine - MD
Position4                  <p>Hospice of Hendersonville County - Medical Director</p>
Position4City              Henderson, NC
MedicalSchoolYear          1976
InternshipCity             Shreveport, LA
InternshipYear             1979
ProfessionalAssociations   {05E5C7FA-99DC-47C7-AC5E-2DA51D87DD1F}|{E519CAD2-C25F-4ADD-BD91-A4DEF861517B}|{67D7F01D-1695-4963-A149-EDF18354BBCC}

感谢您的关注。

Answer 1

听起来你想要PIVOT数据

SELECT
    ItemId,
    UnderGradYear,
    Position1,
    FellowshipCity1,
    FellowshipYear1
    -- add 'columns'
FROM
    data -- this 'table' is the result of current query
PIVOT
(
    MAX(value)
    FOR field IN ([UnderGradYear], [Position1], 
                  [FellowshipCity1], [FellowshipYear1]) --  add 'columns'
) PivotTable

demo

然后您可以使用 SSIS 创建上述结果的 CSV 文件并通过 FTP 传输该文件。

编辑:或者你也可以使用 pivot transformation在 SSIS 中而不是在 T-SQL 中