Showing posts with label transform. Show all posts
Showing posts with label transform. Show all posts

Friday, March 30, 2012

Please Help me Conversion CrossTab Access to SQL Server 2000 Query

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

Please Help me

Thank You

Hi Sam,

Today, my recommendation is the same one that I gave you yesterday. Click here to check it out. Several folks have taken their time and given you suggestions -and you don't seem to be learning from those suggestions.

In fact, I'll post it again:

I suggest that you take the time to work through the suggestions that have been provided to your previous posts until you understand the process.

When you work through the process and begin to understand the process, you will be able to work these out for yourself. (Otherwise, we might think that you are just trying to get folks to do your work for you. And that probably wouldn't be fair to you ...)

|||not understand the process Function TRANSFORM and PIVOT?please help me

thank you

|||

Transform is NOT a Transact-SQL keyword -in this situation, you can probably safely ignore it.

PIVOT is a SQL 2005 keyword that displays rows as columns.

You 'should' have a copy of SQL Server 2005 Books Online. You can get a lot of good information from that source.

A couple of folks indicated to you in your other posts that IIF was not a SQL function (it is an Access function), and they offered suggestions about how to turn IIF functions into CASE structures. Perhaps looking in Books Online and reading about using CASE would be useful.

I, for one, am willing to assist those that seem intent on learning and helping themselves. BUT I get annoyed to repeated be asked to do someone's work for them -UNLESS, of course, I'm getting paid for it.

Just in case others here are not so compelled as I, I shall refrain from additional comments on your posts. Good luck.

|||

This query will help you...

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join OILPALM as [?] On [?].Year=? and [?].NAME=[Main].NAME '
Select @.PreparedSelectQuery =',Count([?].BLOCK) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From OILPALM Group By Year

Exec ('Select [Main].NAME' + @.SelectQuery + ' From (Select Distinct NAME From OILPALM) as [Main]' + @.JoinQuery + ' Group By [Main].NAME')

|||This query help me....Thank you so much...

Saturday, February 25, 2012

Pivot transform not putting all values on same row

I have a pivot transform that it believe is configured correctly but is not distributing the values accross the columns on the same row. for example.

input:

id seqno codevalue

1 A red

1 B red

2 C blue

2 A green

2 B violet

3 A green

desired output:

id Seq_A Seq_B Seq_C

1 red red null

2 green violet blue

3 green null null

what I am getting:

id Seq_A Seq_B Seq_C

1 red null null

1 null red null

2 green null null

2 null violet null

2 null null blue

3 green null null

I do have the pivot usage for the id column set to 1. I have the pivot usage for seqno column set to 2 and codevalue column set to 3. I have the source column for each of the output columns set to the lineageID of the apprpriate input columns. I have the pivotKey values set for each of the destination columns. A for column Seq_A, B for column Seq_B, C for column Seq_C. All four columns have sortkey positions set; 1 for id, 2 for Seq_A, 3 for Seq_B and 4 for column SEQ_C.

It seems like the id column's pivot usage is not set to 1 like it should but when I check it is 1.

I also have several other pivot transforms in the same data flow and they are working as expected.

I have a suspicion that there is some hidden meta data that is messed up that is over-ridding my settings (just my guess) I have deleted this transform and re-done it several times, checking each configuration value, but still getting the same result.

Need some help or thoughts on making this work.

Thanks

Do you need to trim seqno before using it in a pivot? Might there be trailing white spaces or anything?|||

The input source has the column trimmed to char(1); I have done a dataviewer on the input and the output, and the input looks good. I have also recently (yesterday) installed SP2.

In my package, the pivot transform is after a union all transform. However, I have checked the output of the union all with a data viewer and the data input to the pivot transform looks good.

Earlier in my development of this dataflow, I did have some problems with the data source for this pivot transform, but I fixed it. And then checked the output of the pivot and found that it was not right. That is when I deleted the pivot transform and re-created it. But I still had the problem. I have since re-checked every configuration value in the pivot transform and the upstream and downstream transforms. All look to be configured correctly, but still the Pivot is putting each value on it's own row and not across the same row, for a given id.

pivot transform help

I need to transform the following layout by hopefully using the pivot transform, but am confused about the editor ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7

A 2007 1 Y N N N

A 2007 2 Y Y N N

A 2007 3 N N N Y

into this....

A 2007 1 col4 Y

A 2007 1 col5 N

A 2007 1 col6 N

A 2007 1 col7 N

A 2007 2 col4 Y

A 2007 2 col5 Y

A 2007 2 col6 N

A 2007 2 col7 N

A 2007 3 col4 N

A 2007 3 col5 N

A 2007 3 col6 N

A 2007 3 col7 Y

Can I do this using the pivot transform? Any suggestions?

The easiest way to accomplish this will be to transform the compound key into a single column key using the derived column transformation. You would then perform the pivot , then merge the dataflow back to include the compound key.

EDIT: It looks like you want the UNpivot transformation (that which takes you to a more normalized state). see http://technet.microsoft.com/en-us/library/ms141723.aspx

|||

Indeed the unpivot is exactly what I needed. Data flow is now as follows....

OLE DB selects the data

connect to Unpivot

Inside Unpivot editor, my PKcol1-PKcol3 above you check the pass-through box on the right. The other columns (col4-7) you check the other box. Then name the destination column, and put in values if you don't want the "Y" & "N".