CODEWITHSUNDEEP
sqlsql-serverssissql-server-2008-r2
fusion27
fusion27

Reputation: 2636

Pulling results as rows, easy conversion/translation to columns?

I've been handed a task where I've been asked to make use of an SSIS 2008 package to create a CSV then FTP that CSV, following this tutorial here.

My query isn't quite ready.

I'm pulling results where the columns we're going after are coming out as rows. Been reading around on how to to do this with a cursor + stored procedure, but was wondering if anyone has conquered this obstacle with a simpler solution?

Query

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}

Thank you for looking.

Upvotes: 3

Views: 55

Answers (1)

T I
T I

Reputation: 9933

Sounds like you want to PIVOT the data

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

you can then use SSIS to create a CSV of result of above and FTP that file.

Edit: Alternatively you could also use the pivot transformation in SSIS rather than in T-SQL

Upvotes: 4

Related Questions