Convert Rows to Columns
-
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], VariableIDWhich 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 pvtORDER 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
-
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 pvtYou 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://www.sqlservercentral.com/articles/T-SQL/63681/ -
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
-
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.
-
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 DocumentIDSteve Anderson
- stephen.aa - Monday, July 10, 2017 9:34 AM
Not sure what you mean. Do what on variables? You don't use variables in this script. just table fields.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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
- stephen.aa - Monday, July 10, 2017 12:10 PM
Use a CTE for the upper portion of the query, and then join to CTE.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
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
-
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply