Self-Join To Split Cost In Different Columns By Category?

Jan 7, 2015

I've a table similar to the one below, with a SKU, Category and Cost, and need using a simple select command, split the cost in two columns one for each category (1,2), I used a self-join, and it works, but it doesn't show values not equal in both categories

Declare @Tmp_SKUCatValue Table(
SKU char(7)
,Cetegory Int
,Unit_cost Decimal
);
INSERT INTO @Tmp_SKUCatValue (SKU, Cetegory,Unit_cost)
Values
('sku-001',1,120)

[code].....

The result is as

SKU----------UCost_Cat1-----UCost_Cat2
sku-001------120--------------222
sku-002------126--------------228
sku-003------132--------------234
sku-004------138--------------240
sku-005------144--------------246
-----------------------------------------------------------

but missing the following lines,

SKU----------UCost_Cat1-----UCost_Cat2
sku-006------333--------------null
sku-007------null--------------444

Is ok to not show sku-008 as it is not part of category 1 or 2?

View 2 Replies


ADVERTISEMENT

Join Category Table So That Categories Are In The Result On Only

Feb 22, 2007

SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name

FROM Image, JobTag, Tag, Job, Tagcat

WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID
AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID

ORDER BY Image.ImageID, Tag.SortOrder


I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?

View 1 Replies View Related

Speed Cost For Using Or Clause And Functions On Join Statement

Jul 23, 2005

Select memberfrom NameListInner join Memberson (Left(Namelist.NameID,5) = Members.IDOR (left(namelist.SSN,9) = Members.ssnOR (Left(namelist.CustID,9) + '*01' = Members.CustID)wherenamelist.name <> ''How do I speed up a process like this? Can I create indexes on themembers table based on a functionLike an index based on the left(members.id,5)or should these statements go into the where clause?

View 2 Replies View Related

Performance Related Doubt On The Following Items --&&> LEFT/INNER JOIN Vs IN And Cost Of CONVERT

May 28, 2008

Hi,

1.
Right now in my queries I am using lots of LEFT Joins and INNER JOINs... and I was suggested to look at 'IN'... But with IN I did face some performance issues previously and stopped using it... but I have got new doubts on which query will give me better performance...

A query using LEFTJoin or a query using IN/NOT-IN


2.
This question is about CONVERT...

I have a stored proc which is used for updating a table... and multiple columns [of the same table] and corresponding values are sent to the proc [only a subset of the columns might be sent for updates everytime and the columns to update is not fixed for each run of the SP]...

I have to construct a UPDATE String out of it using string concatenation to finally be able to use "sys.sp_executesql" on that update statement...

This results in me having to use CONVERT() lots of times... and one of the columns among them on which I am doing a CONVERT is of the type XML...

So the question is as follows...
a. Is it preferrable to construct a single UPDATE statement string and execute it using "sys.sp_executesql"
b. Or Is it preferrable to give multiple UPDATE statments... i.e. one update statement for each column [Depending on whether that column has to be updated for that run or not]


i.e. The question essentially is:

Does a single update query constructed using lots of CONVERTS [Basically on INT and XML types]
give more performance over using multiple UPDATE statments on the table
Or is it the other way round..

Thanks,
Pratap.

View 5 Replies View Related

T-SQL (SS2K8) :: Finding Last Cost By Article And Compare With Invoice Line Cost?

May 28, 2015

I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.

This is my script:

With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl

[code]....

the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.

Example:

invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075

That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.

View 4 Replies View Related

SQL 2012 :: Cost Threshold For Parallelism - Subtree Cost

Jul 3, 2014

I right in thinking that if the estimated subtree cost is higher than the cost threshold for parallelism then it will use a parallel plan? If so, I've read the cost threshold is measured in minutes but is the subtree cost measured in something else, the mysterious cost number? And if so, how are the two compared?

View 9 Replies View Related

Category/Parent Category Design And Querying

Jun 29, 2005

Hi,

I have a simple table:

Categories
----------
CategoryID
ParentID
Name

I want to associate my Products to a category so that I can search by category....ok.  If I just have a simlpe table:

CategoryProducts
-------------------
CategoryId
ProductId

I can link a product to a category.  Now, if I just link a product to a single category, such as the bottom leaf category:

Self Help / Personal Development / Spiritual / Meditation

I would link a product to the Meditation category.  However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation.  So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation.  My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories.  I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?

Thanks,

jr.

View 5 Replies View Related

SQL 2012 :: Split Data From Two Columns In One Table Into Multiple Columns Of Result Table

Jul 22, 2015

So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:

**TABLE_ONE**
Type Animal TestID
-----------------------------------------
Mammal Goat 1
Fish Cod 1
Bird Chicken 1
Reptile Snake 1
Bird Crow 2
Mammal Cow 2
Bird Ostrich 3

**Table_Two**
Test_name TestID
-------------------------
Test_1 1
Test_1 1
Test_1 1
Test_1 1
Test_2 2
Test_2 2
Test_3 3

In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID

I am trying to create a table such as shown below:

Test_Name Bird Reptile Mammal Fish
-----------------------------------------------------------------
Test_1 Chicken Snake Goat Cod
Test_2 Crow Cow
Test_3 Ostrich

This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.

For e.g

Select
Test_Name AS 'Test_Name',
Table_Bird.Animal AS 'Birds',
Table_Mammal.Animal AS 'Mammal',
Table_Reptile.Animal AS 'Reptile,
Table_Fish.Animal AS 'Fish'
From Table_One

[Code] .....

The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.

View 4 Replies View Related

OUTER JOIN Plus Split? - How To?

Jan 18, 2007

Hi,

I have two tables.. one has a list of the UK postcodes.. well..it's actually the districts..

so it's the first two three or four characters (basically all UK postcodes are one or two letters.. then one or two numbers.. then a gap.. and then some more info)

So my first table has everything "before the gap" in seperate records.



My second table is full of exact postcodes.. so everything before and after the gap.



What I need to do is.. for each "full" postcode I have... strip it down so I get it formatted to everything before the gap.. and then find out how many of those I have...

This is also complicated because many of the "full postcodes" don't have the gap.





so if I have:

er56 7tr
fr2 4tg
er567tr
po3 8ty

I would want results of:

er56 - 2
fr2 - 1
po3 - 1

One I have that, I can bring in the second table with all of the parcial postcodes to do an OUTER JOIN and get the postcodes where I DONT have any results.



I know that's pretty complicated, so I'm stuggling a little!



sO i need to get the first "X" amount of characters out of a db value (it could be two values.. could be up to four- first one or two will be char, second one or two will be numeric), then do a count.



Can anyone help?


View 4 Replies View Related

How To Split Value In Columns

Sep 7, 2013

A#B#C
A#B
A#B#C#D

How do I split above value in columns by "#"?

I need output:

Column1 Column2 Column3 Column4
A B C
A B
A B C D

View 4 Replies View Related

Merge Join Conditional Split - HeadBanger

Jan 31, 2007



I have a merge join that does a full outer join. I then have a conditional split that will breakout by unchanged, insert and update. The update is what I am having a problem with. The conditional split for the update is a follows:

!ISNULL(HISTORICAL) && !ISNULL(TRANSFORM)

Now I believe the problem is related to spaces in the key field let me explain.

The join field is defined as [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

There was an original mismatch between the two tables of VARCHAR(14) & Char(14) but now there both Char(14).

What's interesting is that the few records with Alpha characters correctly does the update but the records with starting numeric data all go down the insert path.

The data of the join columns look like

'308811151 ' - 5 Spaces This Data incorrectly goes to insert path

'TSTRWR02 ' 6 Space This Data correctly goes to update

The data is grouped and sorted by the Key field and the historical and transform column contain a 1 for all records.
while the Doc_nbr which is the key


I tested the join in SQL server and it works with and without the spaces.

From SSIS I also tried RTRIM in the SQL command of the source in addition to substring the first 9 with the right trim and I always received the same results.

I am guessing there is something going on with the not isnull in the conditional split but I can't figure it out if I am missing something or if this is a BUG.


Three paths of data after the merge join and the conditional split

Insert
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
206352185 ,,1,
209900165 ,,1,


Update
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
NEE ,1,1,NEE
New ,1,1,New


Unchanged
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
,1,,0000521096
,1,,0000685216

Any suggestions, am I missing the obvious?

LL











View 3 Replies View Related

T-SQL (SS2K8) :: Split Value Into Two Columns

Mar 30, 2015

I want to display full name column as FirstName and LastName.

Eg if Full Name is Abhas Jadhav then i want to convert it into FirstName- Abhas and LastName - Jadhav

i.e. Before Space is FirstName and After Space is LastName.

View 9 Replies View Related

Split A Column Into 2 Columns

Jul 23, 2005

Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit

View 6 Replies View Related

Split The String Into Columns

Feb 6, 2008




I have a table called products with the values like

ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J

I need to display each productid's with

ProductId ProductName
10 A

20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J

I will be appreciated if you can send me the code.

Thanks,
Mears

View 5 Replies View Related

Split The Data Into Columns

Feb 6, 2008

I have a table called products with the values like

ProductId ProductName
10 A
20 D,E,F,G
30 B,C
40 H,I,J

I need to display each productid's with

ProductId ProductName
10 A

20 D
20 E
20 F
20 G
30 B
30 C
40 H
40 I
40 J

I will be appreciated if you can send me the code.

Thanks,
Mears

View 10 Replies View Related

Split Address Value Into Two Columns

Nov 14, 2007

I am trying to get an address field into 2 colums. I need the number value in one column and street name in another column.

The data is stored:
876 blue ct
9987 red dr
23 windyknoll

This is what I haveelect
substring(Address,0,charindex('',Address)) as number
,substring(Address, (charindex('',Address)+1)
,len(Address)) as address
from contact

View 7 Replies View Related

Query Split Column In 2 Columns In SQL

Jan 22, 2008

I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that.
I have a table like this:
Select Name from Cars;
Result:
Col1
BMWMercedesFordAudi
But i like to make a query so it is displayed like this:
Col1                Col2
BMW               FordMercedes         Audi
So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.

View 1 Replies View Related

Split One Column Into Multiple Columns

Jan 28, 2008

Hi all,
I have a requirement like this  ,
I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469
I have to split this into 3 more columns like(Address1,name,phoneno)--
Means i have 4 columns including Address Column.(Address,Address1,name,phoneno)
 
Example:
Address:Rajkot
Address1:Univercity Road
Name:Mr. K KK Tank Guntur Jal Bhavan
PhoneNO:9843563469
 
How can i acheive this one with out data lose in Address Column.
Thanks in advance.
 
 
 

View 2 Replies View Related

How To Split Columns Into Multiple Rows

Jun 7, 2008

SOURCE TABLE
ID DESCRIPTION
1 I am a programmer
2 I am a doctor

Destination Table

ID LINE DESCRIPTION(Varchar10)
1 1 I am a pro
1 2 grammer
2 1 i am a doc
2 2 tor


Please someone help me on this.

View 4 Replies View Related

Break /split From A Cell Into Columns

Feb 21, 2008

hi,

i have labels for data stored in one cell
eg: item1; item22; item231;
and i want to convert it in following output
(probably using substring and charindex)

No_question| item_position | label
1|1| item1
1|2| item22
1|3| item231

any idea?

View 2 Replies View Related

Split Row's Columns Across Multiple Tables

Feb 7, 2006

Hello,

Hoping someone here can help. Perhaps I'm missing something obvious, but I'm surprised not to see a data flow task in SSIS for splitting *columns* to different destinations. I see the Conditional Split task can be used to route a *row* one way or another, but what about columns of a single row?

As a simple and somewhat contrived example, let's say I have a row with twelve fields and I'm importing the row into a normalized data structure. There are three target tables with a 1-to-1 relationship (that is, logically they are one table, but physically they are three tables, with one of them considered the "primary" table), and the twelve input fields can be mapped to four columns in each of the three tables.

How do I "split" the columns? The best way I can see is to Multicast the row to three different OLE-DB Destinations, each of which inserts to one of the three target tables, only grabbing the four fields needed from the input row.

Or should I feed the row through three successive OLE-DB Command tasks, each one inserting into the appropriate table? This would offer the advantage, theoretically, of allowing me to grab the identity-based surrogate primary key from the first of the three inserts in order to enable the two subsequent inserts.

Thoughts?

Thanks in advance,
Dan

View 5 Replies View Related

MS Access Detail Split Into Columns On Report

Aug 19, 2004

I would appreciate any help on this project. I have created an Access database that contains one vehicle. I have also included all options on that vehicle, which are in one column. Therefore the main criteria for the vehicle is listed each time for each different option. On my report I am grouping by the vin and placing the main criteria in the group header area of the report. The options are going into the detail section. How do I get the options to print in two columns within the detail section? I am unable to find any help on this subject, so I am asking you for help.

Thank you all! Timpy

View 1 Replies View Related

Split A Single Column Data In To 2 Columns

Jan 13, 2005

Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive

FName
John?Doe

FName LName
John Doe

thanks for the help
prit

View 7 Replies View Related

SQL 2012 :: Split CSV Records To Separate Columns

Jun 12, 2014

I've a table as below

custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7

How to split csv values to new fields. so that the desired output should be as below

custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7

View 9 Replies View Related

DB Design :: How To Split One Column Into Two Columns In Server

Mar 6, 2015

I have a ipaddress column is there where i need to split the column into two columns because of values like below

172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874

I have written the below query but it will throuh some error.

  select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0 
   from IPADDRESS
error:
Msg 537, Level 16, State 2, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

View 16 Replies View Related

Split Comma Separated Values Into Columns

Feb 16, 2008



Hi,
I have data like this in my table:

AppId Gender
1 x
2 y

3 x, y
4 x, y, z

I need to transform like this:
AppID Gender
1 x
2 y
3 x
3 y
4 x
4 y
4 z

How to do this?

Thanks in advance

View 10 Replies View Related

T-SQL (SS2K8) :: Split Pipe Delimited String Into Two Columns

Mar 6, 2014

I have a single string "XYZ00001|Test_b|XYZ00002|Test_a|XYZ00003|Test_c" that will continue to grow over time.

Is there a way I can extract the values from the string into two separate columns?

XYZ0001 Test_b
XYZ0002 Test_a
XYZ0003 Test_c

View 5 Replies View Related

SQL Server 2008 :: IPAddress - How To Split One Column Into Two Columns

Mar 6, 2015

I have a ipaddress column is there where i need to split the column into two columns because of

values like below

172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874

172.26.248.8,Fe80::7033:acba:a4bd:f874

I have written the below query but it will throw some error.

select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS

error:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

View 1 Replies View Related

Transact SQL :: Split Rows By Day / By Datetime And Partition By Columns

Jul 22, 2015

I am trying to spilt records into days by the start - End datetime.

I would send an image and data but because I am new to the forum, I am blocked sending images.

"Body text cannot contain images or links until we are able to verify your account"

How I can forward an image.

View 15 Replies View Related

Transact SQL :: Split String As Columns And Insert Into Table

Aug 20, 2015

I have a string ,want to split the values after every space as column value and insert them into a table 

 1306453 0 0 0 0 0

col1      col2  col3 col4  col5 col6
1306453    0         0       0         0       0

View 7 Replies View Related

Split A Column String-value Into Multiple Columns / Inserting In Another Table

Aug 14, 2012

I have a table with a string value, where all values are seperated by a space/blank. I now want to use SQL to split all the values and insert them into a different table, which then later will result in deleting the old table, as soon as I got all values out from it.

Old Table:

Code:
ID, StringValue

New Table:

Code:
ID, Value1, Value2
Do note: Value1 is INT, Value2 is of nvarchar, hence Value2 can contain spaces... I just need to split on the FIRST space, then convert index[0] to int, and store index[1] as it is.

I can split on all spaces and just Select them all and add them like so: SELECT t.val1 + ' ' + t.val2... If I cant find the first space that is... I mean, first 2-10 characters in the string can be integer, but does not have to be.Shall probably do it in code instead of SQL?Now I want to run a query that selects the StringValue from OldTable, splits the string by ' ' (a blank) and then inserts them into New Table.

Code:
SELECT CASE CHARINDEX(' ', OldTable.stringvalue, 1)
WHEN 0 THEN OldTable.stringvalue
ELSE SUBSTRING(OldTable.stringvalue, 1, CHARINDEX(' ', OldTable.stringvalue, 1) - 1)
END
AS FirstWord
FROM OldTable

Found an example using strange things like CHARINDEX..But issue still remains, because the first word is of integer, or it does not have to be...If it isn't, there is not "first value", and the whole string shall be passed into "value2".How to detect if the very first character is of integer type?

Code:
@declare firstDigit int
IF ISNUMERIC(SUBSTRING(@postal,2,1) AS int) = 1
set @firstDigit = CAST(SUBSTRING(@postal,2,1) AS int)
ELSE
set @firstDigit = -1

[code]....

View 2 Replies View Related

SQL Server 2008 :: Split Comma Separated String Into Columns?

Apr 24, 2015

Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.

A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.

The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.

I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.

This needs to be repeated for each row

SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest

Results:

IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119

View 8 Replies View Related

SQL Server 2014 :: Query To Split String As Rows And Columns

Oct 19, 2015

I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters.
Currently am using the below function and getting the parameters by each in select statement as mentioned below.

select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1,
[dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2
CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex]
(
@CustomProp varchar(max),

[code]....

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved