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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply