Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query
Aug 18, 2015
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
View 4 Replies
ADVERTISEMENT
Dec 1, 2015
I have this query:
SELECT
ID1,
ID2,
type,
(case when type = '1' then sum(value) else '0' end) as Value1,
(case when type = '3' then sum(value) else '0' end) as Value2,
(case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y'
GROUP BY ID1, ID2, Type
Which returns:
ID1 ID2 Type Value1 Value2 Value3
005 11547 0 0.00 0.00 279.23
005 11547 1 15.23 0.00 0.00
005 11547 3 0.00 245.50 0.00
And I want to obtain this result:
ID1 ID2 Value1 Value2 Value3
005 11547 15.23 245.50 279.23
View 5 Replies
View Related
Aug 7, 2015
I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....
declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
[code]....
View 4 Replies
View Related
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
Nov 17, 2015
In C#, I can convert UTC time to a specific timezone using:
TimeZoneInfo localTimeZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time");
DateTime UTCTime = TimeZoneInfo.ConvertTimeToUtc(localTime, localTimeZone);
Is there any equivalent function in T-SQL (SQL Server 2014 / SQL Azure)?
Specifically, I want to be able to convert a time into UTC before inserting it into a table. Right now I am doing:
INSERT INTO Incident (IncidentDescription, dtOccurred)
VALUES (@IncidentDescription, DATEADD(hour, 8, @dtOccurred))
I don't want to hardcode "8" and I want it to adjust for daylight savings time (DST).
Is there any concise solution that does not require me to define SQL functions or have lookup tables? I also don't want to use the difference between GETDATE() and SYSUTCDATETIME(), because the timezone of my server is not my local timezone.
View 5 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Jul 11, 2015
I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40
week2: 24
week3: 24
week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
View 3 Replies
View Related
May 13, 2015
I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
View 6 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
View 14 Replies
View Related
Oct 8, 2015
How can I convert the following data
Create Table tbl_Customer_Bank_Details
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo] varchar(50)
)
[Code] ...
into the following format
Create Table tbl_Customer_Bank_Details2
(
[CustomerID] int,
[CustomerName] varchar(50),
[AccountType] varchar(50),
[BankAccountNo1] varchar(50),
[Code] ....
I am looking at this [URL]
how to do this in SQL 2000.
View 16 Replies
View Related
Apr 24, 2015
i have a table with dob and test results , i am trying to pull the data from the table and converting rows columns , below is the table i am using . i used to pivot to do this .
create table #TEST_RESULTS
(ID INT,NAME VARCHAR(10),DOB DATETIME,DAYS_SINCE_BIRTH_TO_TEST INT,TEST_RESULTS INT )
INSERT INTO #TEST_RESULTS
VALUES(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 0 ,1)
,(1,'A','2015-01-01' , 1 ,3)
,(1,'A','2015-01-01' , 2 ,6)
[code]...
View 9 Replies
View Related
Aug 27, 2015
i have following table columns and i want to convert these all columns into row
SELECT [CASHINHAND]
,[CASHATBANK]
,[DEPOSITS]
,[ACCRECEVABLE]
,[SUNDRYDEBTORS]
,[LOANANDADVANCES]
[Code] ....
required output looks like
CASHINHAND 118950
CASHATBANK 200
DEPOSITS 3000
ACCRECEIVABLE
25000
View 3 Replies
View Related
Jul 22, 2015
I have one of the sample values in my table. I need to convert below value to Decimal(18,5)
DECLARE @i
VARCHAR
SET @i
= '0.9'
Output i m looking for is 0.90000
View 16 Replies
View Related
Jun 17, 2015
Using the following tables and data---
CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')
[Code] ...
And currently outputs;
Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million
Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
View 7 Replies
View Related
Aug 10, 2015
I have two queries as below;
SELECT EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours
FROM dbo.tblEventStaffRequired;
and
SELECT EventID, Role, StartTime, EndTime, Hours, COUNT(ID) AS Booked
FROM tblStaffBookings
GROUP BY EventID, Role, StartTime, EndTime, Hours;
How can I join the results of the two by matching the columns EventID, Role, StartTime and EndTime in the two and have the following columns in output EventID, Role, EventDuty, Qty, StartTime, EndTime, Hours and Booked?
View 4 Replies
View Related
Feb 7, 2012
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies
View Related
May 19, 2015
I'm trying to run something like this:
Select ID, FIRST(forename), FIRST(surname) from table1
GROUP BY ID;
I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.
There are records like:
ID forename surname
--------------------------------
1 John Kormack
1 James Dope
2 Erin Dupes
3 Will Hugh
3 Walter Heisenberg
So I want to pull out:
1 John Kormack
2 Erin Dupes
3 Will Hugh
How can I run it in TSQL?
View 6 Replies
View Related
Dec 8, 2006
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
View 2 Replies
View Related
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
May 12, 2015
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol]
(
@vCat NVARCHAR(4000)
)
RETURNS @PList
AS
BEGIN
SELECT @PList += N', [' + [ColumnName] +']'
FROM [ETL].[TableDef]
WHERE [IsActive] = 1
AND [Category] = @vCat
RETURN;
END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
View 13 Replies
View Related
Jul 11, 2001
My table:
Table1
T1T2T3T4T5
---------------
abcde
How to query to have following result:
Table2
abcde
---------------
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
T1T2T3T4T5
Thanks in advance.
Janasha H
View 1 Replies
View Related
Sep 19, 2015
Select DATEPART(year, OrderDate) As Years,SO.TotalRevenue
From Sales.Orders S
Cross Apply (Select Sum(SD.Qty*SD.unitprice-SD.discount) As TotalRevenue From Sales.OrderDetails SD where S.orderid = SD.orderid
) SO
Group by DATEPART(year, OrderDate),SO.TotalRevenue
View 7 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Oct 5, 2015
I have a table with 3 columns (ID Int , Name Varchar(25), Course Varchar(20))
My source data looks like below
ID Name Course
1 A Java
1 A C++
2 B Java
2 B SQL Server
2 B .Net
2 B SAP
3 C Oracle
My Output should look like below...
ID Name Course(1) Course(2) Course(3) Course(4)
1 A Java C++
2 B Java SQL Server .Net SAP
3 C Oracle
Basically need t-sql to Convert non fixed rows to non fixed columns...
Rule: IF each ID and Name have more than 1 course then show it in new columns as course(1) course(2)..Course(n)
Create SQL:
Create table Sample (ID Int null , Name Varchar(25) null, Course Varchar(20) null)
Insert SQL:
INSERT Sample (ID, Name, Course)
VALUES (1,'A','Java'),
(1,'A','C++'),
(2,'B','Java'),
(2,'B','SQL Server'),
(2,'B','.Net'),
(2,'B','SAP'),
(3,'C','Oracle')
View 12 Replies
View Related
Feb 24, 2015
SELECT first.name,first.country, second.name, second.string
FROM
first LEFT OUTER JOIN second
ON
first.name = second.name
WHERE
first.date='2015/02/24'
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.
View 5 Replies
View Related
Nov 14, 2015
Please refer to the below query. I want to filter inner join rows based on outer query column value (refer to bold text).
SELECT M.MouldId, SI.StockCode, MI.Cavity, MI.ShotCounter, CQ.SumOfCastedQty as CastedQty, MI.CounterStartup
FROM MouldItem MI
JOIN (SELECT JD.MouldId, JC.StockCode, SUM(JS.CastedQty) AS SumOfCastedQty
FROM JobCasting AS JS INNER JOIN JobCreationDet AS JD ON JS.JobDetId = JD.Uniid INNER JOIN JobCreation AS JC ON JD.JobIdx = JC.Uniid
[Code] ....
View 2 Replies
View Related
Nov 20, 2015
I have a table (edw_sbx_bt.jjenkins_OVERAGE2) of 40,000 MA_NUM.
I want to find occurrences of those specific MA_NUM in another table (edw_ar_fl.ARTBASE) with specific attributes (CONTRACT_NUM = '593' and TRANSACT_STATUS_CD = 'D').
When I run the query below it returns 4 million rows. How can I write a query that will return the 40,000 MA_NUM (in edw_sbx_bt.jjenkins_OVERAGE2) with the sum total of MA_PAID_AMT associated with each?
SELECT edw_ar_fl.ARTBASE.MA_NUM,
edw_ar_fl.ARTBASE.MA_PAID_AMT
FROM edw_ar_fl.ARTBASE
JOIN edw_sbx_bt.jjenkins_OVERAGE2
ON edw_ar_fl.ARTBASE.MA_NUM = edw_sbx_bt.jjenkins_OVERAGE2.MA_NUM
WHERE edw_ar_fl.ARTBASE.CONTRACT_NUM = '593'
AND edw_ar_fl.ARTBASE.TRANSACT_STATUS_CD = '
View 4 Replies
View Related
Oct 23, 2015
I want to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1. It is some tricky because the value is NULL in some records of NewVol1 and NewVol2.
Update query. SAP, Code, Code1, Code2 and SAPdate are keyfield when join both table to update the NewVol1 and NewVol2 in table #Rec2 from NewVol1 and NewVol2 of table #Rec1.
Create table #Rec1 (SAP char(10), Code char(6), Code1 char(6), Code2 char(6), SAPDate datetime, NewVol1 char(20), NewVol2 char(20))
insert into #Rec1 values ('SATRIP','AAA','AA','A','2014-01-01','838.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','BB','B','2014-01-01','839.88484884',NULL)
insert into #Rec1 values ('SATRIP','AAA','CC','C','2014-01-01','848.88484884',NULL)
insert into #Rec1 values ('SATRIP','BBB','AA','A','2014-01-01',NULL,'383.48377373')
insert into #Rec1 values ('SATRIP','BBB','BB','B','2014-01-01',NULL,'385.48377333')
insert into #Rec1 values ('SATRIP','BBB','CC','C','2014-01-01',NULL,'675.48377234')
[code]....
View 4 Replies
View Related
Jun 23, 2015
I have a table like this:
ID Order Date .....
1 Bla 2015-01-13 12:00:45.2713558 .
2 Mio 2015-01-13 12:05:45.2713558 .
3 Tala 2015-01-13 14:00:45.2713558 .
4 Bla 2015-01-13 15:00:45.2713558 .
5 Mio 2015-01-13 20:00:45.2713558 .
6 Bla 2015-01-15 17:00:45.2713558 .
7 Bla 2015-01-15 19:00:45.2713558 .
[code]...
I tryed several things with CASE, IF, EXISTS,... but I had no success.
View 13 Replies
View Related
Nov 29, 2015
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
View 4 Replies
View Related
May 18, 2015
I have persons who speaks multiple languages and they are in one table, each row is added if he/she speaks multiple languages. Instead I want to add additional columns and load the data.(what I have shown in the desired output)
name language
-------------
ron english
ron french
ron spanish
andy english
andy hindi
kate english
Desired output
name language1 language2 language3 language4 language5 language6
-----------------------------------------------------------------
ron english french spanish
andy english hindi
Kate english
View 4 Replies
View Related
Sep 26, 2006
Hello,
I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
Thank you SO much for your help!
View 5 Replies
View Related
