I have following sample data
I want this data updated to different table based on PK value relationship like below,
What shall I do to make such update ? Here is the sample script I want ot use
DECLARE @Activity TABLE(PK BIGINT,Value1 NVARCHAR(10),Value2 NVARCHAR(10))
INSERT INTO @Activity(PK) VALUES(1)
INSERT INTO @Activity(PK) VALUES(2)
INSERT INTO @Activity(PK) VALUES(3)
[Code] .....
I have a simple table of yearly financials to transpose:
Year/Field Financial1 Financial2 Financial3...
2014 1000000 2000000 3000000
2013 1000000 2000000 3000000
2012 1000000 2000000 3000000
....
The number of years available is dynamic. I would like to retrieve it as follows:
2014 2013 2012
.....
Financial1 1000000 2000000 3000000
Financial2 1000000 2000000 3000000
Financial3 1000000 2000000 3000000$
What is the simplest way to achieve this?
I wondered if it is possible to convert/transpose a list into a 1 row. I have attached a small sample of data and what outcome I would like.
View 19 Replies View RelatedI need to pivot my records in columns into rows. Here is sample data:
create table #columnstorows
(
Payer varchar (5),
ID varchar (5),
ClM varchar (2),
Paid1 float,
Paid2 float,
Paid3 float
)
[code]....
Desired result:
Payer1 Payer2
ID1 ID2
Paid1A Paid1B
Paid2A Paid2B
Paid3A Paid3B
U001 U002
001 002
76.58 19.53
153.48 96.43
53.48 200
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query:
Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT
From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f
where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT
5501 07/13/2015 CO 11 23.87
5501 07/13/2015 PR 12 3.76
5501 07/13/2015 OT 32 33.45
2032 07/14/2015 CO 12 23.87
2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
OTH_PAYER_ID PAID_DATE GROUP_CODE_1 REASON_CODE_1 ADJUSTMENT_AMOUNT_1 GROUP_CODE_2
REASON_CODE_2 ADJUSTMENT_AMOUNT_2 GROUP_CODE_3 REASON_CODE_3 ADJUSTMENT_AMOUNT_3
5501 07/13/2015 CO 11 23.87 PR 12 3.76 OT 32 33.45 2032 07/14/2015 CO 12 23.87 OT 14 43.01
Using DB2.
Hi Guys
I am having a bit of a delimma and am wondering if there is someone out there that could suggest how i could write a SQL statement that would alow me to return data in a certain way,
I have data as follows...
FIrst problemi is the Date in the LogDate COlumn is of Text Type not Date..
Second problem is i need to take this data and transform it to look like the table below
RCDIDEmployeeIDLogDateLogTimeTerminalIDInOut
411 07/23/200620:45:02iGuard# IN
421 07/23/200620:46:17iGuard# OUT
431 07/23/200620:48:08iGuard# IN
441 07/23/200620:48:18iGuard# OUT
451 07/23/200620:48:24iGuard# IN
461 07/23/200620:48:30iGuard# OUT
471 07/23/200620:48:36iGuard# IN
481 07/23/200620:48:41iGuard# OUT
501 07/23/200620:49:57iGuard# IN
511 07/23/200620:50:14iGuard# OUT
521 07/23/200620:59:34iGuard# IN
531 07/23/200620:59:40iGuard# OUT
Employee IDDateInOutIn OutTotalTimeIn
123/07/200620:3520:3620:3820:3900:02
Basicaly i need to transpose it..
If anyone has the sql or knows the sql on how to do this i would be very greatful
Hi all,
Help me out, i am trying to get all values in Table A and insert into table B, i though of writing cursor. see endof the message
Table A:
CREATE TABLE [dbo].[M_SCANNEDSURVEY_AP] (
[M_CustomerSurveyID] [bigint] NULL ,
[SurveyID] [bigint] NULL ,
[LoadStatus] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[3] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[4] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[5] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[6] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[7] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[8] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[9] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[10] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[11] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[12] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
Table B:
CREATE TABLE [dbo].[M_RESPONSE] (
[M_CustomerSurveyID] [bigint] NOT NULL ,
[SurveyID] [bigint] NOT NULL ,
[SeqNumber] [bigint] NOT NULL ,
[Response] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateSubmitted] [datetime] NULL
) ON [PRIMARY]
GO
I want to insert everything in table A to table B
M_CustomerSurveyID -> M_CustomerSurveyID
SurveyID -> SurveyID
I will hardcode insert 1,2,3,4,5,6,7,8,9,10,11,12 for sequence number
values of 1,2,3,4,5,6,7,8,9,10,11,12 -> Response
DateSubmitted -> DateSubmitted
Declare @Count_Scan INT
BEGIN
-- Get the count of scanned data in scanned data table with LoadStatus = N
SELECT @Count_Scan = COUNT(*) from M_SCANNEDSURVEY_APTEST where
LoadStatus = 'N'
IF @Count_Scan > 0
BEGIN
DECLARE ScanData_Cursor CURSOR FOR
SELECT * FROM M_SCANNEDSURVEY_AP WHERE LoadStatus = 'N'
OPEN ScanData_Cursor
FETCH NEXT FROM ScanData_Cursor
While (@@Fetch_Status <> -1)
Begin
If (@@Fetch_Status = -2)
Begin
FETCH NEXT FROM ScanData_Cursor
Continue
End
CLOSE ScanData_Cursor
DEALLOCATE ScanData_Cursor
END
SET @CountSuccess = 'Y'
END
I am supplying you with Sample Data:-Initial Classcode SampleSize Average------- ---------- -------------------------------ADK SSC 22 3.6800000000000002ADK TSC 17 2.7599999999999998ADK TSM 5 3.5499999999999998ANB FCA 31 3.23ANB FCB 50 3.0499999999999998ANB FCC 30 3.0899999999999999ANB SCA 35 3.02ANB SCB 9 3.4300000000000002ANB TCA 30 2.77ANB TCB 6 1.8799999999999999APG MCH 10 3.8300000000000001APG TSCH 9 4.21AUG FCC 30 3.5499999999999998AUG SCA 28 2.7800000000000002AUG SCB 29 3.4300000000000002AUG SCC 30 2.8999999999999999AUG TCA 30 2.8599999999999999AUG TCB 29 2.1200000000000001AVK TSP 12 3.6200000000000001BKK FS 32 2.52BKK TSM 5 3.3799999999999999BSK SSP 28 3.1200000000000001BSK TSP 12 3.0600000000000001------- ---------- -------------------------------These are the averages of teachers grouped by initial.Maximum 7 averages are related to each teacher.Ignore the column SampleSize.Using this output is it possible to get output like this:Initial Class1 Avg1 Class2 Avg2 Class3 Avg3 Class4 Avg4..ADKTSA 1.4 TSB 2.5 TSC 4.5 SSC 5.0..ANB SSA 1.4 SSB 2.5 NULL NULL NULL NULL..APG.........................AVK....................BKK....................BSK.....................Since the maximum class nos are 7, those having less than classes willcontain NULL in the class and average field.Is it possible to carry out this in single query?-Sameer
View 1 Replies View RelatedI'm relatively new to this, so bear with me here. (SQL Server 2005 Express, Datatypes are all varchar, int or money, nothing crazy...)
I currently have a table (not designed by me...) which looks like this:
ProjectID Months Expenses
3214 JAN 45.67
3214 MAR 56.78
1234 JAN 78.99
4567 MAY 43.56
And so on.... And I need this:
Project ID Jan Feb Mar Apr May Jun etc....
3214 45.67 56.78
1234 78.99
4567 43.56
I had attempted to do it using this code (really sloppy... I know. Beginner's attempt...)
DECLARE @Months varchar(4)
DECLARE @Counter int
DECLARE @Rowcount int
EXEC @RowCount=dbo.ReturnRowCount
SET @Counter = 0
WHILE @Counter <= @RowCount
BEGIN
SET @Counter=@Counter+1
SET @Months=(SELECT Months FROM TestData WHERE RowNum=@Counter)
SELECT @Months
WHILE @Months='JAN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jan)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='FEB'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Feb)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Mar)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='APR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Apr)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAY'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, May)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jun)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUL'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jul)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='AUG'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Aug)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='SEP'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Sep)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='OCT'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Oct)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='NOV'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Nov)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='DEC'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Dec)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
SET @Months=''
END
SELECT RowNum, ProjectID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM SusansOutputTable
I also tried using IF statement instead of the imbedded WHILE statement and had the same result. What it's returning is 98,765 results of the same thing, with the exception of the last 25 rows, which return what I expected (which still needs to be collapsed...) I know there HAS to be an easier way to do this, I'm afraid it might be a bit beyond me though, any help?
Ps. ReturnRowCount function just returns the row count of the base table, assuming a rowid column with an IDENTITY int variable, which I can safely assume in this case. Also, the counter seems to work fine, but something is wrong in my internal portion of the while loop I think...
In our contract management system, each contract has over 100 reference fields attached to it. These are all stored in single table with contract ID, reference GUID and value as the columns.
So you will have multiple rows for each contract....one for each of the reference fields and then the value attached to that reference.
I want to return the data so there is one row per contract with the reference fields as columns and the reference field values as the column data.
Can this be done using PIVOT as I have tried but not had any success?
I have been tasked with writing an update query to update a table with more than 150 million rows of data. Here are the table structures:
Source Tables :
OC
CREATE TABLE [dbo].[OC](
[OC] [nvarchar](255) NULL,
[DATE DEBUT] [date] NULL,
[DATE FIN] [date] NULL,
[Code Article] [nvarchar](255) NULL,
[INSERTION] [nvarchar](255) NULL,
[Code] ....
The update requirement is as follows:
DECLARE @Counter INT=0 --This causes the @@rowcount to be > 0
while @@rowcount>0
BEGIN
SET rowcount 10000
update r
set Comp=t.Comp
[Code] ....
The update took more than 48h and didn't terminate , how to accelerate it ?
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
I have a temp table like this
CREATE TABLE #Temp
(
ID int,
Source varchar(50),
Date datetime,
CID varchar(50),
Segments int,
Air_Date datetime,
[code]....
Getting Error
Msg 102, Level 15, State 1, Procedure PublishToDestination, Line 34 Incorrect syntax near 'd'.
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.
Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
Hello,
I have a table that looks like this:
Customer Treas
------- -----
1010 Merch
1010 Price
1020 Merch
1030 Promo
And I need to add a period column so that the table looks like this.
Customer Treas Period
------- ----- ------
1010 Merch 1
1010 Merch 2
1010 Merch 3
1010 Price 1
1010 Price 2
1010 Price 3
1020 Merch 1
1020 Merch 2
1020 Merch 3
1030 Promo 1
1030 Promo 2
1030 Promo 3
etc. Of course there are 12 periods.
I'm thinking this is related to cross-tab or transpose function somehow? Once again I am clueless to the point I find it difficult to even research the problem on my own.
Hopefully this makes sense.
Thanks!
Besides using a cursor, is there a way to transpose a short result set to a comma delimited list?
For example, assume SELECT color FROM [tblColor] yields:
Red
Green
Blue
Black
My desired result is really:
Red, Green, Blue, Black
Any clues?
Hello
I am currently importing an excel table into sql server in the form
date code header1 header2
2004 5AX num1 num2
2004 5AZ num3 num4
......
I am reading this into an sql table fine. However I want to convert its format into the following
date code field value
2004 5AX header1 num1
2004 5AX header2 num2
2004 5AZ header1 num3
2004 5AZ header2 num4
......
The number of header1, header2,..... is dynamic so I cannot hard code the number of headers there are.
Has anyone got any ideas. I have got some code already which will give me the number of headers there are but that Im afraid is as far as I have got before I get stuck
Thanks in advance for any help
Richard
How to transpose a table. My table is like
taskid date1 hrs1 date2 hrs2 date3 hrs3 emp
1 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 AAA
2 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 BBB
I want the result as
date1 date2 date3
emp taskid hrs1 hrs2 hrs3
I'm playing around with the following tables and need some advice.TablePeople (5000 records)peopleIDsexageraceTableExam (5000 records)examIDdatepeopleIDTableQuestions (5000 records)questionIDquestionFieldname (QFN0001,QFN0002,QFN0003, ... ,QFN5000)TableAnswers (25,000,000 records)examIDquestionIDanswerSQL Query Statement Needed!Query the database to return fields from TablePeople and TableAnswers(from 1 to 255 questionFieldnames)Example Query and return data from:ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011I know how to join the tables and return the following recordsetExamID, sex, age, race, QuestionFieldName, AnswerID100001,M,40,White,QFN0001,0100001,M,40,White,QFN0002,5100001,M,40,White,QFN0003,6100001,M,40,White,QFN0005,3100001,M,40,White,QFN0007,5100001,M,40,White,QFN0011,4100002,M,55,White,QFN0001,1100002,M,55,White,QFN0002,4100002,M,55,White,QFN0003,3100002,M,55,White,QFN0004,4100002,M,55,White,QFN0007,5100002,M,55,White,QFN0011,6etc105000,M,48,White,QFN0001,5105000,M,48,White,QFN0002,4105000,M,48,White,QFN0003,6105000,M,48,White,QFN0005,3105000,M,48,White,QFN0007,4105000,M,48,White,QFN0011,5We would like the resulting table to look like the following:ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011100001,M,40,White,0,5,6,3,5,4100002,M,55,White,1,4,3,4,5,6etc105000,M,48,White,5,4,6,3,4,5Any suggestions appreciated.Gregory S. MoyInformation Processing ConsultantEpiSense Research ProgramDepartment of Ophthalmology & Visual SciencesUniversity of Wisconsin - Madison
View 1 Replies View RelatedIs there anything like transpose in SSRS.
The result of my t-sql queries gives me many columns and less rows
i.e there are four quaters as rows and there are around 10 columns like sales , customer , material ....
So if i can transpose columns to rows and rows to columns some how then the report will be more presentable according to me.
Can anyone please help me out.
Hi
I have a "transaction" table of diagnostic tests, each row consisting of one test performed on one person as follows:
Patient Test Time
ID ID of Test
--------------------------
1 1 May 1
1 2 May 1
1 4 May 2
2 5 etc.
3 1
3 3
4 4
4 2
I'd like to transpose this into a table with one row per patient, so I can then describe, for each patient, the sequence of tests that he or she had, e.g.
Patient T1 T2 T3 T4 T5 TEST_SEQUENCE
1 1 2 4 - - "1 2 4"
2 5 - - - - "5"
3 1 3 - - - "1 3"
4 4 2 - - - "4 2"
How do I do this in SQL, preferably without having to specify the maximum number of tests? (In a statistical package we use, this is called a table "reshape" operation)
Thanks in advance.
Updating the Table . I need to update Two Column .. DEPT and Product .
For a Same EmployeeName DEPT and Product Name should Repeat till Next Value in encountered.
Example For EmployeeName 'A' Dept Value 100 should Repeat till 20 comes, based on Date, because 100 comes before 20 and Project P1 should repeat till P2 comes.
Below is the Sample Source Table.
CREATE TABLE #MYTEMP3 (NAME VARCHAR(50), DEPT INT ,PROJECT VARCHAr(100), ORDERDT DATE)
INSERT INTO #MYTEMP3
SELECT 'A',100,'P1', '2015-01-01' UNION ALL
SELECT 'A','','', '2015-01-02' UNION ALL
SELECT 'A','','', '2015-01-03' UNION ALL
[Code] ....
Looking for UPDATE Query . I do not want to use Common table expression to achieve this .
Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table
ID type
1 A
1 B
2 A
2 C
2 D
etc,....
I want to tranpose
to some like
ID type
1 A,B
2 A,C,D
etc,...
or
ID type1 type2 type3
1 A B
2 A C D
etc,....
Really appreciate any input.
I have the below table:
Pallet1 ItemA1
Pallet1 ItemB1
Pallet1 ItemC1
Pallet1 ItemD1
Pallet1 ItemE1
Pallet2 ItemA2
Pallet2 ItemB2
Pallet2 ItemC2
Pallet2 ItemD2
Would like to Transpose it to
Pallet 1 ItemA1 ItemB1 ItemC1 ItemD1 ItemE1
Pallet 2 ItemA2 ItemB2 ItemC2 ItemD2 NULL
Would be thankful for Any help.
I am looking to transpose a row into multiple columns. I have a member's data who might be associated with multiple labs for that one member. Instead of having multiple records for that member, I would like to have one record per member with multiple rows for the lab data.
View 16 Replies View RelatedI have recordsID Sku Name Date2 41 Blair 01/04/033 45 John 03/04/03that should look like...ID 2 3Sku 41 45Name Blair John.....and so on. Number of source rows will be fixed (12) so no of targetcolumns will be 12 too.Anyone know of a quick way to do this via simple SQL ?
View 3 Replies View RelatedHi Friends,How to transpose the columns into rowsi want to convert the table which looks like thisID Name HomePhone WorkPhone Email1 test1 678364 643733 Join Bytes!2 test2 678344 643553 Join Bytes!to a table which should look like thisID Name Device1 test1 6783641 test1 6437331 test1 Join Bytes!2 test2 6783442 test2 6435532 test2 Join Bytes!Thanks in AdvanceArunkumar
View 1 Replies View RelatedI would like to make an after update trigger on a table (ARD TABLE) where depending on the criteria the trigger will either insert, update, or delete a row in the trigger table. Basically, in the trigger table there can only be rows where the column status is > 0. So, if the ARD TABLE has a row updated that used to have a status of 0 and now the status changed to 1 then insert it into the trigger table. If the ARD TABLE had a row that used to be 1 and now it's 0 then delete it from the trigger table. And if the ARD TABLE had a row that was 1 and now made any change to that row we need to update the corresponding row in the trigger table.
View 4 Replies View RelatedI am trying to update all records in #newtable that exist in #mastertable. I have been using Intersect to show me the duplicate records, but now I need to update a field in #newtable This was my syntax to show the records that exist in both tables, how can I change this to an update statement? The field in #newtable I want to update is [alreadyexists] and I want to update it with a yes value
So update #newtable set [alreadyexists] = 'yes'
select uno, mucha, pablo, company from #Newtable
intersect
select uno, mucha, pablo, company from #mastertable
I have a table that has two columns. One column has ID from 1 to 1800. The other column is null. I want to update the second column with values from 29 to 43. So for ID 1, value will be 29, ID value will 30 and it goes to 43. Then after it will start from 29 again and goes to 43. It goes all the way to the highest ID, i.e. 1800.I have added a script for better clarity
declare @t table (stuID int, valueID int)
insert into @t (stuID, valueID)
values (1,null), (2,null), (3,null), (4,null)
the null part is what I want to have from 29 to 43 .... all the way untill the ID reachs to 1800
I have a below table, I have RowOrder for master entity and need to update child entity,example : if master has RowOrder 1.00 and there are 2 child before 2.00 , then child entites RowOrder will be 1.01 & 1.02 and so on...
DECLARE @TBL TABLE (RowNum INT, DataId int, RowOrder DECIMAL(18,2) NULL)
INSERT INTO @TBL VALUES
(1, 105508, 1.00),
(2, 105717, NULL),
(3, 105718, NULL),
(4, 105509, 2.00),
(5, 105510, 3.00),
(6, 105514, NULL),
(7, 105513, 4.00),
(8, 105719, NULL),
(9, 105718, NULL),
(10,105718, NULL)
SELECT * FROM @TBL
I need to update RowOrder and looking for below output,
how to transpose rows to columns . there are 800 dynamic rows
device_idUserNameipaddrmacaddrLastConnecttimeOperatingSystemRAMCPUTOTALSIZEFREESPACEpercentageDriveNameName
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 ActiveX [15.0.0.239]
C0YGCEOOLT7CWQXTELENORT87585010.84.108.288086f21ee6003/19/2015 9:30:08 AMMicrosoft Windows 7 Professional Version 6.1.7601 [Build 7601] [Service Pack 1]8,090GenuineIntel[2500MHz]12,742,291,456.00906,027,008.007D:Adobe Flash Player 15 Plugin [15.0.0.239]
[code]...