March 7, 2013 at 4:26 am
Hello everybody,
I'm using two dynamic Pivot queries(which are basically the same except for the filter on date) to build a final result.
Both dynamic Pivot produce a result with 12 columns where the first one is a nvarchar and the other 11 columns are values.
My final result for the column names must be like the following: sta_rep,0,1,2,3....23
See attachment for more details.
Thank you.
March 7, 2013 at 4:51 am
The problem is that you are using
Select
Table1.*
,Table2.*
From
Table1
JOIN Table2 on Table1.Col1=Table2.Col2
This will result in the sta_rep column being output, as you know the Column List for both sides then you can build some Dynamic SQL to return only the desired columns
this is a rough stab at what you need to do but it needs testing.
DECLARE @SQL_Instruction varchar(max)
SET @SQL_Instruction = CONVERT(varchar(max),
'Select ##VEN_REP_TRANSPOSED.*, '
+REPLACE(@ColumnVEN_AP_REP,'sta_rep,','')
+' FROM ##VEN_REP_TRANSPOSED
LEFT OUTER JOIN ##VEN_AP_REP_TRANSPOSED
ON ##VEN_AP_REP_TRANSPOSED.sta_rep
=##VEN_REP_TRANSPOSED.sta_Rep')
EXEC (@SQL_Instruction)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2013 at 6:14 am
Hi Jason and thanks for the reply.
Your code is good and it works but it solves one part of the question the one that removes the sta_rep column.
What about the second question(renaming the columns names)?
Thank's again
March 7, 2013 at 6:55 am
When you build the column list you could build them with an alias in using a ROW_NUMBER() over (ORDER BY).
Create Table #TDP_AP
(
periodo varchar(10)
)
Insert into #TDP_AP
Values('201201')
,('201202')
,('201203')
DECLARE @columnsVEN_AP_REP_Alias varchar(max)
SET @columnsVEN_AP_REP_Alias
= STUFF((SELECT ',['+ periodo
+'] AS ['+CONVERT(nvarchar(50),ROW_NUMBER() OVER (ORDER BY periodo))+']'
FROM #TDP_AP FOR XML PATH('')),1,1,'')
Print @columnsVEN_AP_REP_Alias
This way you would put the @ColumnsVEN_AP_REP in the Alias, @queryVEN_AP_REP this would then alias it on the ## table.
Do the same on the first query and calculate an Offset to plug in to the ROW_NUMBER() on the second query and it should work a treat.
hope that makes sense.
Edit : removed the RIGHT('00000'+ <rn>,5) as you DONT need the Leading 0's on the columns.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2013 at 7:08 am
It seems a little bit complex
anyway for the second question i'll use a different approach:
I'll create a #temp table with the columns sta_rep,1,2,3,4...etc and than use a insert into.
Somthing like the following:
CREATE TABLE #VEN_RESULT_FINAL
(sta_rep nvarchar(4) NULL,
[1] decimal(18, 4) NULL,
[2] decimal(18, 4) NULL,
[3] decimal(18, 4) NULL,
[4] decimal(18, 4) NULL,
[5] decimal(18, 4) NULL,
[6] decimal(18, 4) NULL,
[7] decimal(18, 4) NULL,
[8] decimal(18, 4) NULL,
[9] decimal(18, 4) NULL,
[10] decimal(18, 4) NULL,
[11] decimal(18, 4) NULL,
[12] decimal(18, 4) NULL,
[13] decimal(18, 4) NULL,
[14] decimal(18, 4) NULL,
[15] decimal(18, 4) NULL,
[16] decimal(18, 4) NULL,
[17] decimal(18, 4) NULL,
[18] decimal(18, 4) NULL,
[19] decimal(18, 4) NULL,
[20] decimal(18, 4) NULL,
[21] decimal(18, 4) NULL,
[22] decimal(18, 4) NULL,
[23] decimal(18, 4) NULL,
[24] decimal(18, 4) NULL
);
INSERT INTO #VEN_RESULT_FINAL
SELECT * FROM ##myresult;
I understand that doing of the table it's resource consuming but in my case the result will have less than 200 rows so this is not a problem.
Thank's again Jason.
March 7, 2013 at 7:17 am
No problem, Its what ever is best for your environment and organisation.
The problem with the table is that you stated it was for a dynamic, number of columns so by creating a flat table you're introducing a limiting factor.
You also still have the problem of numbering.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2013 at 7:20 am
The number of columns is fixed, the names of the columns are changing for every query.
Thanks again Jason,
Have a nice day.
March 7, 2013 at 8:13 am
Sorry my misunderstanding about the columns.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 7, 2013 at 8:15 am
No problem Jason :smooooth:
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy