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


ADVERTISEMENT

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 :: 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

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

Transact SQL :: Returning A Column Value Based Upon The Precedence Value Of Another Column?

Nov 4, 2015

#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):

View 6 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 :: 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 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 :: 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

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

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 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

Transact SQL :: Grouping Based Of Column Value

Nov 5, 2015

Below is the data I have in table name

TeamStatus
T 1 Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
T 1 Pick Up
T 1 Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate

I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,

Group TeamStatus
Group 1 T 1Complete or Escalate
T 2 Pick Up
T 2 Resolve Case

Group 2 T 1Pick Up
T 1Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate

View 7 Replies View Related

How To Get Additional Rows Based On Column Val

Mar 13, 2006

Hello,

I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?

I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example

EdgeID Desc TermType ReltTotID

1Global Edge Model w/ Fwd Earn II T 1
2Short Term Global Edge Model w Fwd Earn IIS 1
3Long Term Global Edge Model w Fwd Earn IIL 1
4Emerging Market Edge Model w Fwd Earn T 4
5Short Term EM Edge Model w Fwd Earn S 4
6Long Term EM Edge Model w Fwd Earn L 4
19SmallCap Edge Model w/ Fwd Earn T 19
20SmallCap Short Term Edge Model w/ Fwd EarnS 19
21SmallCap Long Term Edge Model w/ Fwd EarnL 19
35Global+EM Edge Model w Fwd Earn T 35


The final query result is :



EdgeID Description Short Desc PerID UnivID DefID

1Global Edge Global Developed 500622355938
4Emerging Market Emerging Markets 500632356039
19SmallCap Edge Small Cap Edge 500642364244


I would like it to be :

1Global Edge Global Developed 500622355938
2Short Term Global Developed NULL2355938
3Long TermGlobal Developed NULL2355938
4Emerging Market Emerging Markets 500632356039
5Short Term Emerging Markets NULL2356039
6Long Term Emerging Markets NULL2356039
19SmallCap Edge Small Cap Edge 500642364244
19Short Term Small Cap Edge NULL2364244
19Long Term Small Cap Edge NULL2364244


The stored proc query is as below:


SELECT
EdgeModelID = em.EdgeModelID
--, EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID

FROM OptMod..GO_EdgeModels em

JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1

JOIN OptMod..GO_EdgeModelDisplayParameters emdp
ON emdp.EdgeModelID = em.EdgeModelID
AND emdp.ParameterName = 'NewEdge32 Screening'


LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
ON emdn.EdgeModelID = em.EdgeModelID
AND emdn.ParameterName = 'NewEdge32 Display Name'


LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID

LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID

LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'

WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID



Thanks in advance!!!
sqlnovice123

View 1 Replies View Related

Multiple Rows Combined Based On A Column

Sep 6, 2011

I have a table with two columns refid and name and it has the following values

1 tom
1 jim
2 bob
1 bob

I need a resultset that would have the following values

1 tom, jim, bob
2 bob

I have tried couple of things one being:

DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist

But I am looking for a resultset with a unique refid and all the names comma separated for that refid.

View 2 Replies View Related

Split A Row Into Multiple Rows Based On Column

Sep 5, 2014

I need to split a row into multiple rows based on multiple column values.

time_id = 111
employee_id = 222
time_in = 10:00
time_out = 16:00
break1_in = 12:00
break1_out = 13:00
break2_in = 14:00
break2_out = 15:00

I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.

row 1
time_in 10:00
time_out 12:00

row 2
time_in 13:00
time_out 14:00

row 3
time_in 15:00
time_out 16:00

View 2 Replies View Related

Filtering Out Duplicate Rows Based On Three Column

Nov 25, 2014

I want to filter out the duplicate rows based on three columns. I got this quick query from Microsoft site to filter out the duplicate rows, but I am getting the result that filters out the non-duplicate one too. Below is the query

;With Temp as (
SELECT row_number() over (partition by [id],[p_date], order by [id],[p_date],) as Row,

[code]...

In the above case id is null, but in some rows id is not null . The above is obviously not duplicate.

View 8 Replies View Related

Duplicating Rows Based On A Integer Column

Dec 17, 2007

I have a table that holds a number of offers made to a orginization for placements at a lecture. what im wanting to do is have each of the rows for a orginization repeated so that the names of people attending can be put into the database.

The first table would looks something like this:

id___|__orginization_| lecture | offers
1 | orga | lec1 | 5
2 | orgb | lec1 | 3
3 | orga | lec2 | 3


the result im looking to get is something like this where the name of the attendess would be inputed in an application.
id | orginization | lecture | nameofattende
1 | orga | lec1 | j. blog
2 | orga | lec1 | s. smith
3 | orga | lec1 | h. samual
4 | orga | lec1 | j. sams
5 | orga | lec1 | b.j. james
6 | orgb | lec1 | m. curry
7 | orgb | lec1 | k. murry
8 | orgb | lec1 | g. hansen



Can anyone help with this

cheers

colin

View 4 Replies View Related

Transact SQL :: How To Reset Running Total To 0 Based On Other Column Value

Nov 9, 2015

I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.

here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.

Here is the example of data along with required column

MonthId Flag CalcVal 

201401 1 1
201402 1 2
201403 1 3
201404 1 4
201405 1 5

and so on .........

View 8 Replies View Related

Transact SQL :: Update Column Based On Row Number For Previous Row

Jul 25, 2015

Below is the resultset I got using the following SQL statement

SELECT   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1

Resultset:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL

[Code] ....

Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18

[Code] ....

View 4 Replies View Related

Transact SQL :: Populate Column On Insert Based On Value From Another Table

Jul 26, 2015

I have the following 2 tables:

Table: classes  Columns: classID, hp
Table: char_active  Columns: name, classID, hp

The classes table is already populated.

What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error

Incorrect syntax near 'inserted'.

I'm new to sql, this is actually the first trigger I've tried writing. 

create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go

View 4 Replies View Related

Transact SQL :: Comparing Previous Records Based On Each ID Column

Aug 21, 2015

I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1

We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID

My tables look like

Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)

Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)

select * from @tab1

1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only

2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column 

3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column 

My expected result set should be like ....

View 10 Replies View Related

Transact SQL :: How To Give Rankings Based On GUID Column

May 22, 2015

I have the table like below I want to gave Rankings to GUID Column. GUID col has Duplicate rows I am not getting unique ranking.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Select Rows Based On One Distinct Column

Apr 18, 2014

--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware

From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.

-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware

CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))

[Code] ......

View 2 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 :: Update Table With Max Value And Row Number (based On 2 Column Partitions)

Sep 15, 2015

I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null

job_cd permit_nbr reference_nbr

ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008

View 3 Replies View Related

Transact SQL :: Send Email To Multiple Users Based On A Column

May 15, 2015

I have a query running and returning 3 columns, user name, e-mail and device name

SELECT DISTINCT v_R_User.Full_User_Name0 AS 'User full Name', v_R_User.Mail0 AS 'E-Mail', _RES_COLL_DEV00144.Name
FROM         v_R_System INNER JOIN
                      v_R_User ON v_R_System.User_Name0 = v_R_User.User_Name0 INNER JOIN
                      _RES_COLL_DEV00144 ON v_R_User.User_Name0 = _RES_COLL_DEV00144.UserName INNER JOIN
                      v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
 Where v_R_User.Mail0 <> ''
ORDER BY 'User Full Name'

From here I would like to generate an e-mail to each user (like mail merge) to each user in the table an include their machine name. I can do it with PS, but rather have it run directly from SQL. Is it possible?

View 9 Replies View Related

Transact SQL :: How To Remove Duplicate Records Based On Date Column

Jul 28, 2015

I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate

I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).

Note: Duplication should be removed based on “Last Hardware Scan” date.

Only latest date should be considered from multiple records for the same system. "

View 4 Replies View Related

Transact SQL :: How To List 3 Derived Columns Based On 4th Common Column

Nov 25, 2015

I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:

Ticket Type  |  Company A Count  | Company B Count  | Company C Count

I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.

DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType

[Code] .....

If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:

TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote

[Code] ...

How to get the query results? Am I even close to getting it right?

View 3 Replies View Related

Transact SQL :: How To Extract Dates From A Single Column Based On Conditions

Oct 13, 2015

The data I have is as follows -

ID1    ID2     Date            Action
100   500     09/08/14     Open
100   500     09/24/14     Close
101   510     07/10/15     Open
101   510     07/19/15     Close

The output I want in a single result set is -

ID1    ID2     Open_Date   Close_Date
100    500    09/08/14      09/24/14
101    510    07/10/15      07/19/15

Any way to do this in T-SQL .

View 10 Replies View Related

Selecting Rows From A Table Based On First 2 Characters Of 12 Char Column

Oct 21, 2013

I have to select rows from a table

if the first 2 characters of a 12 char column are
'GB'

Select BFKEYC from table where

I have a hokey way of doing it but it looks embarrassing:

BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'

View 8 Replies View Related

Transact SQL :: Case Statement For A New Column Based On Date And Username Columns

Sep 2, 2015

Below is the SQL Query i am currently having

SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate

[Code] ....

And here is the result dataset

ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

[Code] .....

So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.

View 13 Replies View Related

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related







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