Transact SQL :: Swap Values In 2 Rows Based On Primary Key

Jun 12, 2015

I need swapping values in rows of tables based on primary.

I will explain my scenario with the below example.

ID          Name                  Value   
---         --------                -------- 

1            name1                 202
2            name1                 203
3             name2                203
4             name2                202
5             name3                204

In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.

In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.

UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END

WHERE
Valuein(202,203)

View 10 Replies


ADVERTISEMENT

Transact SQL :: How To Swap Values Of Two Rows In A Table

Sep 10, 2015

I am a newbie to Sql server and i am having a task where my current table with always two rows existing and ID column as primary key looks like below:

ID  PlateNo Type Image Name

27 455 User img1.jpg
32 542 Alternative img2.jpg

And i want a sql query to modify my table so that the data should be like as shown below:

ID  PlateNo Type Image Name

27 542 Alternative img2.jpg
32 455 User img1.jpg

View 8 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

View 2 Replies View Related

Compare Values In Consecutive Rows And Print Rows Based On Some Conditions

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 1 Replies View Related

Transact SQL :: Getting Primary Key Values As A List Of Values

Oct 14, 2015

I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below

Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.

View 2 Replies View Related

Help, Selecting Rows Based On Values In Other Rows...

Mar 25, 2002

I'm stuck. I have a table that I want to pull some info from that I don''t know how to.

There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').

i.e example, this is the existing data.

Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW

This is the data I want...

Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED

The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.

Cheers,

Chris

View 1 Replies View Related

Transact SQL :: How To Add SUM Column Based On Each Row Values

Nov 2, 2015

I have the following result query :

Which is produce by following SP :

SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,

[code]....

What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?

View 9 Replies View Related

How Can I Swap Two Rows In A Table

May 19, 2005

I need to swap two rows in a table so that i can give the user a choice to move the row data up and down. The top and bottom row values may not be in the serial order. I am using MS Sql 2000. Is there any way to do that or its really difficult big process. I really stuck with this problem can you help me out, please friends.. Your help will greatly appreciated..
Thank you very much

View 3 Replies View Related

Transact SQL :: Accumulate Values Based On Time For Each Day

Jun 10, 2015

I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:

Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2

How can I use a query to get accumulate value for the day? the result should look like this:

Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6  24...
1999-01-01 23:00:00 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00 2 16

The accumulated value should be based on every hour for the day and then repeat for the second day and so on.

What is the best way to do this? I am using SQL 2008

View 2 Replies View Related

Swap Rows For Columns In A Table

May 12, 2008

have the following table:


RecordKey MonthYear SD1 SD2 SD3
1 Jan 2008 6 AA 0
2 Feb 2008 10 BB 3
3 Mar 2008 12 CC 8

etc....



I need to transform it to this:


1 2 3

Jan 2008 Feb 2008 Mar 2008
6 10 12
AA BB CC
0 3 8



Can this be done automatically in SQL?

View 3 Replies View Related

Transact SQL :: Based On One Column Need To Cascade Rows One By One

Sep 23, 2015

Here's the my structure and data as follows

>
SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans 
FROM mytab WHERE mrno = 'MR1514' and tranno = 1111
ORDER BY no_of_trans
tranno medrecno
createdon createdat
nooftrans

[Code] ...

Now requirement is : -

tranno medrecno
createdon createdat
nooftrans     tranno medrecno
createdon createdat
nooftrans

[Code] ....

View 4 Replies View Related

Transact SQL :: How To Repeat Rows Based On Column Value

Apr 30, 2015

I have a query that gives me a result with a column value for example 4.

I now want to repeat this row 4 times with a new column that calculated from 1 - 4.

Or when column value is 3 I want to repeat row 3 times with new column name 1-3

View 7 Replies View Related

Transact SQL :: Merge Two Rows Into One Based On Same Customer Number

Oct 26, 2015

I have a table Customer with below column

CustomerNumber , FName,LName,DOB

I have either 2 or 1 row for each customer number  I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.

example:
CustNumber  Fanme   Lname   DOB
1  Sam tiller 08/26/1981
1  Joe   timy  01/02/1986
2  jack  niks   09/09/1990

Result I want:
CustNumber Fname1  Lname1 DOB1 Fname2 Lname2 DOB2
1  Sam  Tiller 08/26/1981 Joe  timy 01/02/1986
2  Jack  niks  09/09/1990 null null null

View 7 Replies View Related

Transact SQL :: Update One Table Based On Another Table Values For Multiple Values

Apr 26, 2015

I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B. 

View 5 Replies View Related

Transact SQL :: Update A Column Randomly Based On Values From Another Table

Oct 27, 2015

I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work

DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000

[code]...

View 8 Replies View Related

Transact SQL :: Creating Report - Query With Parameters Based Off Values

Sep 1, 2015

I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to  '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.

SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information], 
user8 AS [Transaction Type], user2 AS[Invoice Number], 
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]

[Code] ....

View 2 Replies View Related

Transact SQL :: Update Incoming Rows Based On Percentage Calculation

Apr 2, 2015

This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.

Requirements:

1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.

2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"

System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade Amount 

SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $      25,68,458.15

[Code] .....

View 36 Replies View Related

Transact SQL :: Split Rows Based On Time Difference More Than 1day

Aug 5, 2015

equipmentid downtimestartdate downtimeenddate  dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00            69
b6e4 2015-03-18 13:00 2015-03-20 04:00            39

i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)

equipmentid downtimestartdate downtimeenddate dowtime
a3er          2015-03-15 02:00 2015-03-15 24:00            22 
a3er          2015-03-16 00:00 2015-03-15 24:00            24
a3er          2015-03-17 00:00 2015-03-15 23:00            23

[code]...

View 2 Replies View Related

Insert Multiple Rows To Table Based On Values From Other 2 Tables.

Nov 21, 2007

I have a form to assign JOB SITES to previously created PROJECT.  The  JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES"  which has only 2 columns:  "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked.  The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId    -    SiteId
1   -   5
1    -   9
1    -   16
1    -   18
1    -   20
1    -   27
1    -   31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.

View 10 Replies View Related

Insert Rows Based On Number Of Distinct Values In Another Table?

May 21, 2014

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.

Example:

Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40

Table 1 :

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

how can I update Table 1 to get desired results?

View 2 Replies View Related

SQL Server 2012 :: Transpose Values From Rows To Columns Based On Name

Nov 12, 2014

I am working on a sql data that has a list of product names, shipment type and the count of shipments. The values are listed as rows in the database. it will be in the below format.I want to transpose only the shipment type and the corresponding count of each product name in the below format.I tried to do this but i am not able to achieve in the correct format.

View 6 Replies View Related

Transact SQL :: Return Preset Data Values Based On User Material ID

Jul 22, 2015

I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:

User Material = Wood-MDF
Preset Material = MDF Panel

I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?

View 2 Replies View Related

Transact SQL :: Get Multiple Rows Based On Comma-separated Ntext List In On Column?

Jun 2, 2015

I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122).  I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.

This obviously works:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')

But this does not:

SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)

View 5 Replies View Related

SQL Server 2012 :: Insert Rows Based On Number Of Distinct Values In Another Table

May 20, 2014

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.

Example:

Temp table: (Contains only one field with all distinct values in table 1)

Days_in_warehouse
20
30
40

Table 1 :

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

How can I update Table 1 to see desired results?

View 3 Replies View Related

SQL Server 2012 :: Update Table Based On Existing Values In Multiple Rows?

Oct 1, 2015

The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.

Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-

The CustID is the same.

Order #2 has a more recent order date.

Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).

So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.

I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.

update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,

[code]....

View 4 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

How To Insert Rows To Table B Based On Values In Table A.

Mar 7, 2008



I need Insert rows in the OrderDetails Table based on values in the Orders Table

In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType


For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD



Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.


I need a SQL Script to accomplish this. thanks

View 6 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

Power Pivot :: Calculating Values For Future Dates Based On Past Values

Nov 13, 2015

I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?

View 6 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

Select Values From One Table Based Upon Values In Another...

May 19, 2006

How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?

View 3 Replies View Related







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