Convert Rows to Columns

• stephen.aa

  SSC Eights!

  Points: 815

  More actions

  July 6, 2017 at 3:37 pm

  #402255

  Hello:

  I have this query:
  SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, V.VariableID
    FROM ACME.dbo.Documents F
    INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
                            FROM ACME.dbo.VariableValue
                                                                                                  ) V
                                                                                                  ON V.DocumentID = F.DocumentID
    INNER JOIN (SELECT RevisionNo
                            FROM ACME.dbo.VariableValue
                                                                                                  ) V2
                            ON V.RevisionNo = F.LatestRevisionNo
                                                                                                    WHERE (V.VariableID = 62) -- Number
                                                                                                    OR (V.VariableID = 45) -- Revision
                                                                                                    OR (V.VariableID = 56) -- Description
                                                                                                    OR (V.VariableID = 57) -- Document Number
                                                                                                    OR (V.VariableID = 69) -- Weight
                                                                                                    OR (V.VariableID = 1115) -- Date
                                                                                                    OR (V.VariableID = 67) -- Material
                                                                                                    OR (V.VariableID = 2108) -- Cost
                                                                                                    OR (V.VariableID = 74) -- Vendor
                                                                                                    OR (V.VariableID = 2076) -- Project Name
                                                                                                    OR (V.VariableID = 51) -- Project Number
                                                                                                    OR (V.VariableID = 2109) -- Location
                                                                                                    ORDER BY [Filename], VariableID

  Which produces this result:
  

  I looked at using the PIVOT functionality, which looks like this:
  SELECT DocumentID, [62] AS [Number], [45] AS [Revision], [56] AS [Description], [57] AS [Document Number], [69] AS [Weight], [1115] AS [Date], [67] AS [Material], [2108] AS [Cost], [74] AS [Vendor], [2076] AS [Project Name], 51 AS [Project Number], [2109] AS [Location]
FROM (SELECT VariableID, ValueText, DocumentID
   FROM ACME.dbo.VariableValue
             ) V2
             PIVOT
             (
             COUNT (VariableID) -- I really don't want to count here!
             FOR ValueText IN
             ( [62], [45], [56], [57], [69], [1115], [67], [2108], [74], [2076], [51], [2109] )
             ) as pvt

  ORDER BY DocumentID

  And produces this:
  

  I prefer the first four columns in the first illustration, and want to add the column values, not counts to the rows after that.  I am not sure this is possible, or even the correct approach.  In my query, I have used count only because there seems to be a call for count, or sum, or some other mathematical function.  All I want is the vaklue.

  Thanks in advance for any insights you can provide on this.

  Steve Anderson

• Chris Harshman

  SSC-Forever

  Points: 42179

  More actions

  July 7, 2017 at 7:45 am

  #1949926

  I think the example query you provided accidentally swapped VariableID and ValueText columns in your PIVOT clause.  You probably want:
  PIVOT
(
MAX (ValueText)
FOR VariableID IN
( [62], [45], [56], [57], [69], [1115], [67], [2108], [74], [2076], [51], [2109] )
) AS pvt

  You may want to read about using the Cross Tab method of doing this instead of using PIVOT though, since it is more flexible and can perform better.  There's a good article on it here:
  http://qa.sqlservercentral.com/articles/T-SQL/63681/

• stephen.aa

  SSC Eights!

  Points: 815

  More actions

  July 10, 2017 at 8:36 am

  #1950188

  Hello:

  So this seems to have addressed my issue with the PIVOT portion of this query.  Can you tell me, what is wring here?  It dows not seem to like the parenthesis at the end.

  SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, V.VariableID
FROM ACME.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
     FROM ACME.dbo.VariableValue
             ) V
             ON V.DocumentID = F.DocumentID AND V.RevisionNo = F.LatestRevisionNo
INNER JOIN (SELECT DocumentID, [62] AS [Number], [45] AS [Revision], [56] AS [Description], [57] AS [Document Number], [69] AS [Weight], [1115] AS [Date], [67] AS [Material], [2108] AS [Cost], [74] AS [Vendor], [2076] AS [Project Name], 51 AS [Project Number], [2109] AS [Location]
     FROM (SELECT VariableID, ValueText, DocumentID
       FROM ACME.dbo.VariableValue
               ) V2
               PIVOT
               (
               MAX (ValueText)
               FOR VariableID IN
               ( [62], [45], [56], [57], [69], [1115], [67], [2108], [74], [2076], [51], [2109] )
               ) as pvt
     ORDER BY DocumentID
     )

  Steve Anderson

• cphite

  SSCarpal Tunnel

  Points: 4231

  More actions

  July 10, 2017 at 9:06 am

  #1950193

  You need a table alias after that last parenthesis.  You're creating a derived table there, so it needs an alias.

  Also, you're going to get an error from that ORDER BY being inside the derived table; you'll need to either move the ORDER BY outside, or add a TOP 100 PERCENT to the derived table.

• stephen.aa

  SSC Eights!

  Points: 815

  More actions

  July 10, 2017 at 9:34 am

  #1950197

  Can you tell me what variables to do this on?  This format loses the Pivot Table, and VariableID is not available.

  Thanks!

  SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, V.VariableID
FROM ACME.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
     FROM ACME.dbo.VariableValue
             ) V
             ON V.DocumentID = F.DocumentID AND V.RevisionNo = F.LatestRevisionNo
INNER JOIN (SELECT DocumentID, [62] AS [Number], [45] AS [Revision], [56] AS [Description], [57] AS [Document Number], [69] AS [Weight], [1115] AS [Date], [67] AS [Material], [2108] AS [Cost], [74] AS [Vendor], [2076] AS [Project Name], 51 AS [Project Number], [2109] AS [Location]
     FROM (SELECT VariableID, ValueText, DocumentID
       FROM ACME.dbo.VariableValue
               ) V2
               PIVOT
               (
               MAX (ValueText)
               FOR VariableID IN
               ( [62], [45], [56], [57], [69], [1115], [67], [2108], [74], [2076], [51], [2109] )
               ) as pvt
     ) V3
             ON V3.DocumentID = V.DocumentID
ORDER BY DocumentID

  Steve Anderson

• sgmunson

  SSC Guru

  Points: 110588

  More actions

  July 10, 2017 at 10:52 am

  #1950207

  stephen.aa - Monday, July 10, 2017 9:34 AM

  Can you tell me what variables to do this on?  This format loses the Pivot Table, and VariableID is not available.

  Thanks!

  SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, V.VariableID
FROM ACME.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
     FROM ACME.dbo.VariableValue
             ) V
             ON V.DocumentID = F.DocumentID AND V.RevisionNo = F.LatestRevisionNo
INNER JOIN (SELECT DocumentID, [62] AS [Number], [45] AS [Revision], [56] AS [Description], [57] AS [Document Number], [69] AS [Weight], [1115] AS [Date], [67] AS [Material], [2108] AS [Cost], [74] AS [Vendor], [2076] AS [Project Name], 51 AS [Project Number], [2109] AS [Location]
     FROM (SELECT VariableID, ValueText, DocumentID
       FROM ACME.dbo.VariableValue
               ) V2
               PIVOT
               (
               MAX (ValueText)
               FOR VariableID IN
               ( [62], [45], [56], [57], [69], [1115], [67], [2108], [74], [2076], [51], [2109] )
               ) as pvt
     ) V3
             ON V3.DocumentID = V.DocumentID
ORDER BY DocumentID

  Not sure what you mean.  Do what on variables?   You don't use variables in this script. just table fields.

  Steve?(aka sgmunson)?:) 🙂 :)?
  Health & Nutrition
  Rent Servers for Income (picks and shovels strategy)

• stephen.aa

  SSC Eights!

  Points: 815

  More actions

  July 10, 2017 at 12:10 pm

  #1950225

  Good call, table fields.

  When I do the alias, I need to add ON, such as ON V3.DocumentID = V.DocumentID.
  DocumentID seems to be the only field available to use, but does not allow me to present the data in the pivot format.  The problem is, I do not know the syntax to implement another INNER JOIN with the PIVOT.

  Thanks for the clarification.

  Steve Anderson

• sgmunson

  SSC Guru

  Points: 110588

  More actions

  July 10, 2017 at 12:23 pm

  #1950230

  stephen.aa - Monday, July 10, 2017 12:10 PM

  Good call, table fields.

  When I do the alias, I need to add ON, such as ON V3.DocumentID = V.DocumentID.
  DocumentID seems to be the only field available to use, but does not allow me to present the data in the pivot format.  The problem is, I do not know the syntax to implement another INNER JOIN with the PIVOT.

  Thanks for the clarification.

  Use a CTE for the upper portion of the query, and then join to CTE.

  Steve?(aka sgmunson)?:) 🙂 :)?
  Health & Nutrition
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110588

  More actions

  July 10, 2017 at 12:25 pm

  #1950232

  Also, I don't know for sure exactly what needs to be pivoted.   Just joining the pivot query to the other tables may not work quite the way you want, and that appears to be the case.   You may need to get all the data that you want to pivot into one CTE, and then the final SELECT will do the pivot.

  Steve?(aka sgmunson)?:) 🙂 :)?
  Health & Nutrition
  Rent Servers for Income (picks and shovels strategy)

• stephen.aa

  SSC Eights!

  Points: 815

  More actions

  July 10, 2017 at 2:04 pm

  #1950255

  sgmunson:

  Thank you so much for the help on this.  Here is the solution:

  WITH P AS (SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, V.VariableID
FROM ACME.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
     FROM ACME.dbo.VariableValue
             ) V
             ON V.DocumentID = F.DocumentID AND V.RevisionNo = F.LatestRevisionNo)
           SELECT * FROM P
     PIVOT
     (
     MAX (ValueText)
       FOR VariableID IN
       ( [62], [45], [56], [57], [69], [1115], [67], [2108], [74], [2076], [51], [2109] )
       ) as pvt
  ORDER BY [Filename]

  Steve Anderson

• sgmunson

  SSC Guru

  Points: 110588

  More actions

  July 12, 2017 at 1:20 pm

  #1950625

  Glad I could help.   Let me re-format your query so it appears more consumable for those reading this thread in the future:
  WITH P AS (

      SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, V.VariableID
    FROM ACME.dbo.Documents AS F
        INNER JOIN (
                    SELECT DocumentID, VariableID, ValueText, RevisionNo
                    FROM ACME.dbo.VariableValue
                    ) AS V
                        ON V.DocumentID = F.DocumentID
                        AND V.RevisionNo = F.LatestRevisionNo
)
SELECT *
FROM P
    PIVOT (MAX (ValueText)
        FOR VariableID IN ([62],[45],[56],[57],[69],[1115],[67],[2108],[74],[2076],[51],[2109])) AS pvt
ORDER BY [Filename];

  Steve?(aka sgmunson)?:) 🙂 :)?
  Health & Nutrition
  Rent Servers for Income (picks and shovels strategy)