May 9, 2012 at 12:22 pm
[font="Arial"]Hi guys. I'm brand new to this forum, and for the most part SQL queries. I have been banging my head against a wall trying to get this to work. I have scoured the internet and found examples, but I have been unable to get it to work for me. I am basically trying to look at all of the properties for a specific file. I would more than likely be searching for the file name(s). I am using SQL Server 2008 R2. I need help.
This is my query.
Select Documents.Filename, Attribute.AttributeName, VariableValue.ValueText
From Documents, Attribute, VariableValue
Where Documents.Filename like '%test%'
and Documents.DocumentID = VariableValue.DocumentID
and VariableValue.VariableID = Attribute.VariableID
and Attribute.BlockName = 'CustomProperty'
and VariableValue.ConfigurationID = '2'
Here are my result. (I used dashes for spaces to try and get the formatting to hold)
FilenameAttributeNameValueText
TestPart.SLDPRTAPPROVED_TIME2012-05-08
TestPart.SLDPRTAPPROVED_BYxxxxx xxxxx
TestPart.SLDPRTRevisionA
TestPart.SLDPRTItemCodeB
TestPart.SLDPRTCREATED_BYxxxxx xxxxx
TestPart.SLDPRTCREATION_DATE2012-05-08
TestPart.SLDPRTCutSize5 x 10
TestPart.SLDPRTDescriptionPLATE, 11 GA x 5 x 10, ASTM A36 Steel
TestPart.SLDPRTPRODUCT_GROUPING101
TestPart.SLDPRTFILTER1ASTM A36 Steel
TestPart.SLDPRTGroupType11 GA
TestPart.SLDPRTNested1
TestPart.SLDPRTIDTestPart
TestPart.SLDPRTPRODUCT_CODE6
TestPart.SLDPRTPGroupPLATE
TestPart.SLDPRTIDENT0007
TestPart.SLDPRTSerial000000043
TestPart.SLDPRTWeight1.70
I have been trying to pivot this table to where the AttributeName column is actually the column headers and the ValueText column is shown below. Something like this.
FilenameAPPROVED_TIMEAPPROVED_DATEAPPROVED_BYetc...
TestPart.SLDPRT3:02:43PM2012-05-08xxxxx xxxxx
This would allow me to compare values for multiple files much easier.
Would someone please show me how the query is supposed to look to get it formatted properly?[/font]
May 9, 2012 at 12:27 pm
It is doable!
Please provide some sample data, table structure, business rules and expected result, like this format:
Sample data & table structure:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t
CREATE TABLE #T
(
ColA INT
,ColB INT
,ColC INT
)
INSERT INTO #T
SELECT 1 , 1, 10
UNION ALL SELECT 1 , 2, 11
UNION ALL SELECT 1 , 3, 12
UNION ALL SELECT 2 , 6, 20
UNION ALL SELECT 2 , 8, 21
UNION ALL SELECT 2 , 7, 22
UNION ALL SELECT 3 , 4, 31
UNION ALL SELECT 3 , 4, 32
UNION ALL SELECT 3 , 3, 33
Business Rlule : Get all the rows that has the MAX of ColB for each value of ColA.
Expected Result:
ColAColBColC
1312
2821
3431
3432
May 9, 2012 at 7:54 pm
ColdCoffee is right. You shouldn't burden your helpers with having to set up your DDL and sample data for you. Today I was feeling generous.
Can do with PIVOT or otherwise as follows:
DECLARE @t TABLE
([FileName] VARCHAR(30), AttributeName VARCHAR(30),ValueText VARCHAR(100))
INSERT INTO @t
SELECT 'TestPart.SLDPRT','APPROVED_TIME','2012-05-08'
UNION ALL SELECT 'TestPart.SLDPRT','APPROVED_BY','xxxxx xxxxx'
UNION ALL SELECT 'TestPart.SLDPRT','Revision','A'
UNION ALL SELECT 'TestPart.SLDPRT','ItemCode','B'
UNION ALL SELECT 'TestPart.SLDPRT','CREATED_BY','xxxxx xxxxx'
UNION ALL SELECT 'TestPart.SLDPRT','CREATION_DATE','2012-05-08'
UNION ALL SELECT 'TestPart.SLDPRT','CutSize','5 x 10'
UNION ALL SELECT 'TestPart.SLDPRT','Description','PLATE, 11 GA x 5 x 10, ASTM A36 Steel'
UNION ALL SELECT 'TestPart.SLDPRT','PRODUCT_GROUPING','101'
UNION ALL SELECT 'TestPart.SLDPRT','FILTER1','ASTM A36 Steel'
UNION ALL SELECT 'TestPart.SLDPRT','GroupType','11 GA'
UNION ALL SELECT 'TestPart.SLDPRT','Nested','1'
UNION ALL SELECT 'TestPart.SLDPRT','ID','TestPart'
UNION ALL SELECT 'TestPart.SLDPRT','PRODUCT_CODE','6'
UNION ALL SELECT 'TestPart.SLDPRT','PGroup','PLATE'
UNION ALL SELECT 'TestPart.SLDPRT','IDENT','0007'
UNION ALL SELECT 'TestPart.SLDPRT','Serial','000000043'
UNION ALL SELECT 'TestPart.SLDPRT','Weight','1.70'
SELECT [FileName]
,MAX(APPROVED_TIME) AS APPROVED_TIME
,MAX(APPROVED_BY) AS APPROVED_BY
,MAX(Revision) AS Revision
,MAX(ItemCode) AS ItemCode
,MAX(CREATED_BY) AS CREATED_BY
,MAX(CREATION_DATE) AS CREATION_DATE
-- etc.
FROM (
SELECT [FileName]
,CASE AttributeName WHEN 'APPROVED_TIME' THEN ValueText END AS APPROVED_TIME
,CASE AttributeName WHEN 'APPROVED_BY' THEN ValueText END AS APPROVED_BY
,CASE AttributeName WHEN 'Revision' THEN ValueText END AS Revision
,CASE AttributeName WHEN 'ItemCode' THEN ValueText END AS ItemCode
,CASE AttributeName WHEN 'CREATED_BY' THEN ValueText END AS CREATED_BY
,CASE AttributeName WHEN 'CREATION_DATE' THEN ValueText END AS CREATION_DATE
-- etc.
FROM @t) x
GROUP BY [FileName]
Looks messy I know but I believe that PIVOT would be nearly as hideous. Copy/paste is your friend here though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 10, 2012 at 5:33 am
Thanks for the help guys. I'm sorry about no sample data. As I said before, I am new to SQL, and I wasn't really sure what that meant. Hopefully I'll get better in the future.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply