将结果逐行提取，易于转换/翻译为列？

Question

我被分配了一个任务，要求我利用SSIS 2008包创建CSV文件并将其FTP，根据这里的教程。

我的查询还没有准备好。

我正在提取结果，其中我们要获取的列是作为行输出的。我一直在阅读如何使用游标+存储过程来解决此问题，但想知道是否有人已经找到了更简单的解决方案？

查询

---

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'

Results

---

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}

感谢您的关注。

谢谢。

- fusion27

---

一个存储过程，或者一点 PHP 代码，对于这个问题来说都是非常简单的。 - Strawberry

啊，@Strawberry 我需要进行编辑。由于我们正在使用 SSIS 包，所以尝试在数据库中完成所有操作。感谢您的关注！ - fusion27

在这种情况下，存储过程真的是唯一的解决方案（据我所知）。 - Strawberry

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

演示

接下来，您可以使用SSIS创建上述结果的CSV文件，并FTP该文件。

编辑：或者您也可以在SSIS中使用透视转换而不是T-SQL中的透视。

- T I