Join Two Columns Together To Form A New Column
Feb 1, 2014
I'm trying to join two columns together to form a new column
My code is basically in the form of can't post the actual since it would be cheating--school assignment
SELECT Column1Name,Column2Name, Column3Name,Column4Name,
Column1Name+Column2Name AS NewColumn1
Column3Name+Column4Name AS NewColumn1
FROM OriginalTable
View 1 Replies
ADVERTISEMENT
Oct 27, 2007
Hi I'd like to select two columns out of one column from another table. Example from source table:
Code Block
ID ArtNr EAN
1 4711 51323135
1 4712 84651213
1 4713 84351322
2 4711 86431313
2 4714 23546853
1 4714 54646545
2 4715 64684353
2 4716 65435132
I want to join this table to a base table with one select, and generate the following output. The ID identifies If the ArtNr has an EAN1 or EAN2:
Code Block
ArtNr EAN1 EAN2
4711 51323135 86431313
4712 84651213
4713 84351322
4714 54646545 23546853
4715 64684353
4716 65435132
View 20 Replies
View Related
Oct 12, 2015
I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
View 2 Replies
View Related
May 25, 2015
There is a column named Timings in HH:MM:SS format. Datatype of this column is varchar(50).
I want to sum the rows in this column and get the output as one single record.
00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16
View 4 Replies
View Related
Feb 28, 2007
I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click If Page.IsValid Then ' Define data objects Dim conn As SqlConnection Dim comm As SqlCommand ' Read the connection string from web.config Dim connectionString As String = _ ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString ' Initialize connection conn = New SqlConnection(connectionString) ' Create command comm = New SqlCommand( _ "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _ "CategoryID, SubjectID, Description, StatusID) " & _ "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _ "@CategoryID, @SubjectID, @Description, @StatusID)", conn) ' Use randomize Randomize() Dim randomvalue As Integer ' Generate random value between 999 and 100. randomvalue = Int((900 * Rnd()) + 100) ' Add command parameters comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@First_Name").Value = fnameTextBox.Text . . . comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int) comm.Parameters("@StatusID").Value = randomvalue 'Enclose database code in Try-Catch-Finally Try ' Open connection conn.Open() ' Execute the command comm.ExecuteNonQuery() ' Reload page if the query executed successfully Response.Redirect("HelpDesk.aspx") Catch ' Display error message dbErrorMessage.Text = _ "Error submitting the help desk request! Please try again later, and/or change the entered data!" Finally 'close connection conn.Close() End Try End If End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch' and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue'
View 2 Replies
View Related
Oct 25, 2006
Hi,
I have a little question. I have an application which interfaces with a SQL Express Database. On a form, I want to bind a control which is made of several Radio buttons to a table column which is in fact a varchar(1). This is the picture:
Table column: OptVisualRpt varchar(1)
Screen control: 2 radio buttons
rb_VisRPTbImp_O for "yes"
rb_VisRPTbImp_N for "no"
I'm really scratching my head as how I can bind a single table column to my radio buttons controls. I think that I just can't this way but rather have to use an intermediate variable or control.
Solution 1?
I thought of using a local variable that I would set to "y" or "n" following "CheckedChanged" events fired from radio buttons but I don't know how to bind this variable to my table column.
Solution 2?
I thought of placing a hidden text control into my form, which one would be binded to my table column, that I would also set to "y" or "n" following "CheckedChanged" events fired from radio buttons.
Any of these solutions which would be feasible or any more neat way to do it?
Many thanks in advance,
Stéphane
View 1 Replies
View Related
May 15, 2008
Hi,
I am sure there is a simple answer to this, but I cannot find it at the moment???
I have a simple data table in SQL which gives me Division, PL Measure and Value...
SQL Table
Division
PL_Desc
Result
A
Total Labour Costs
10
A
Total Sales (inc Machine Income)
100
B
Total Labour Costs
5
B
Total Sales (inc Machine Income)
100
C
Total Labour Costs
9
C
Total Sales (inc Machine Income)
100
I need to report on this and calculate a ratio, so I have pushed this into a Matrix Report but cannot work out how to get the ratio column to work???...
Matrix Report
???????????
Division
Total Labour Costs
Total Sales (inc Machine Income)
New Column = Labour / Sales
A
10
100
10%
B
5
100
5%
C
9
100
9%
Grand Total
24
300
8%
If anyone can help save me from my own stupidity!
Cheers, Matt
View 6 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
  1       |John            |Baker
  2       |Carl             |Lennon
  3       |Marion           |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
  1     |1/1/14    |12/30/14
  2     |1/1/15    |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeIDÂ sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeIDÂ Â AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeIDÂ Â AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeIDÂ Â AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Apr 16, 2008
Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris
View 3 Replies
View Related
Dec 28, 2003
There is a query:
SELECT info_cost.Ac_Year, info_paym.Ac_Year FROM info_paym FULL OUTER JOIN info_cost ON (info_cost.ID = info_paym.ID AND info_cost.Ac_Year = info_paym.Ac_Year) WHERE info_cost.ID=1882 OR info_paym.ID=1882
Thus I have a table of two columns which values either the same or one of them equals NULL:
Ac_Year Ac_Year
--------- -----------
NULL 1999-2000
NULL 2000-2001
2001-2002 2001-2002
2002-2003 2002-2003
Can I create a column in the query to join this two columns???
Like this:
year
---------
1999-2000
2000-2001
2001-2002
2002-2003
View 1 Replies
View Related
Apr 2, 2007
I have two colums within the same table. The 1st column contains name of products and the second column is used to rename the products so not every row is entered (omly those products we want to rename). I would like to create a new column which updates the name of the products. So if there is no new name it uses the old one, but if there is a new name use that one. I hope this makes sence?
View 3 Replies
View Related
Aug 2, 2006
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
View 2 Replies
View Related
Jan 11, 2006
I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.
---------------------------
SELECT * FROM jobs
INNER JOIN
(
SELECT contact_id FROM contacts WHERE deleted = 0
)AS ValidContacts
ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)
---------------------------
This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?
Another possible solution is if I were to use "WHERE/IN" like this:
---------------------------
SELECT * FROM jobs
WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0)
OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)
---------------------------
This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.
I'm using SQL Server 2000 on Windows XP Pro.
View 6 Replies
View Related
Apr 13, 2012
I have 2 tables, Licenses and Prices as below
Licenses
User Tariff Licensetype
User1 Tariff1 License1
User2 Tariff2 License1
User3 Tariff1 License2
User4 Tariff2 License2
etc
Prices
Tariff LicenseType Cost
Tariff1 License1 £1
Tariff1 License2 £2
Tariff2 License1 £1.50
Tariff2 License2 £2.50
I need to create a query that will produce a table as follows:
User LicenseType Cost
I have tried the following:
Select Licenses.User, Licenses.LicenseType, Prices.Cost
from Licenses
inner join Prices
on Licenses.Tariff = Prices.Tariff
and Licenses.LicenseType = Prices.LicenseType
[Code] ....
But all three output multiple lines for each user.
View 1 Replies
View Related
Jun 21, 2007
After working with Oracle (can I say that out loud here? ) databases for a couple of years, our company recently switched to SQL2005. So far I really like it, and the switch has been relatively easy for me. Except for one thing...
I'm trying to find out how to join a subquery on multiple columns. In Oracle it would like this:
select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)
But this doesn't work in SQL-T. I've temporarily solved it by doing this:
select * from #temp1 where id_1+id_2 not in (select id_1+id_2 from #temp2)
I don't like concat solutions for joins though (it always feels 'unsafe' to me). Plus, I can't believe there's no way to solve this.
I've searched the FAQ and BOL but I just can't seem to find the solution. Please help. I feel stupid.
View 5 Replies
View Related
Jan 29, 2007
Here's an oversimplified version of a query that I'm writing and wantedto know if there are any performance differences between the two versions.select *from table_a a , table_b bwhere a.col_1 = b.col_1and a.col_1 = 1000versusselect *from table_a a , table_b bwhere a.col_1 = 1000and b.col_1 = 1000All the tests show that they run at the same speed. But I have a verylarge query that joins 5 tables together and I'm trying to get as muchout of it as possible. Currently it runs at 2 seconds which I reallydon't like and would like to get it at under 1 second. So I'm lookingfor every little bit.I've already removed the DISTINCT, which in my test case doesn't doanything, but still took up one second.
View 2 Replies
View Related
Oct 31, 2006
I have a one to many relationships each claim we bill for a client has many status. Things like claim sent, claim received by insurance company, claim rejected. There are 80 different statuses all stored in tbl_status and I need to distill into three columns. I currently have the query below that returns only accepted claims and the date claim was released to insurance company.
I am struggling with how to have multiple columns released; paid, rejected each column can be represented by one or more of the 80 status in the status table.
So I want something like this
Select Case status €˜135€™ or €˜111€™ then Y else N AS Accepted
Select Case status €˜123€™ or €˜444€™ then Y else N AS Released
Select Case status €˜435€™ or €˜909€™ then Y else N AS Aprroved
Select distinct tp.ControlNo, tp.ClaimID, t1.StmtFromDate, sh135.StatusDate
from claims.dbo.tbl_Patient tp Join
(select tc.claimid, tc.StmtFromDate,
from claims.dbo.tbl_Claim tc
where (tc.Name = '6296U1' OR tc.Name = '6296H1')
AND tc.PayerInd = 'A'
AND tc.StmtFromDate BETWEEN '09/01/2006' AND '9/30/2006'
AND tc.SystemCode <> -1 -- Deleted
AND not exists ( Select * from claims.dbo.tbl_StatusHistory sh
where sh.ClaimId = tc.ClaimID AND sh.StatusCode IN('210','844','995','5400','7310','7311','7320','A0','A1','A2','A5','P0','P1','P2','F0','F1','F3','F3F','F4')
) T1 On tp.ClaimID = T1.ClaimId
Left join claims.dbo.tbl_StatusHistory sh135 ON T1.ClaimId = sh135.ClaimId AND sh135.StatusCode = '135'
View 8 Replies
View Related
Sep 12, 2007
I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.
I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.
I have following query which does not produce desired result
SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)
The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.
I'm pretty sure it's simple solution but can not figure out myself.
View 4 Replies
View Related
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
View Related
Oct 14, 2015
LeaveEntitlementID PeriodID LeaveType EmployeeID NumberOfDays
1 1 Annual 1 10
2 1 Annual 1 10
3 1 Sick 2 10
4 2 Sick 2 10
5 2 Sick 2 10
I have the above table (LeaveEntitlement) which has the above columns.
What I want to sum the column NumberOfDays based on EmployeeID, LeaveType and PeriodID columns as of LeaveTypeNumberOfDays.
For example sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Annual
The result should be shown in new column name AnnualLeave (20)
sum(NumberOfDays) where PeriodID=1 and EmployeeID=1 and LeaveType=Sick
The result should be shown in new column name SickLeave (10)
Same all leave Types
The table should be shown as the below after executing the query
LeaveEntitlementID PeriodID EmployeeID AnnualLeave SickLeave
1 1 1 20 0
2 1 2 0 10
3 2 2 0 20
is it possible in sql server
View 8 Replies
View Related
Dec 25, 2007
Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
View 4 Replies
View Related
Jul 19, 2006
Hi everyone,
What is the main difference between first form normalizations and second form normalization ?
In my opinion, they are both generated for the same operation which is to prevent redundancy(in other words; duplication of data in several records).
So would you please explain it to me ?
Thanks
View 1 Replies
View Related
Jan 20, 2015
How to join 2 heap tables with out any common fields.
for example tbl1 has
col1
1
2
3
4
5
and tbl1 2 has
col2
a
b
c
I want the output like
col1 col2
1 a
2 b
3 c
4
5
is this possible with out using row_number()?
View 9 Replies
View Related
Sep 1, 2015
DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))
INSERT INTO @Tab1
SELECT 1, 'A', 'B', 'C'
UNION ALL
SELECT 2, 'D', 'E', 'F'
UNION ALL
[Code] ....
Need query to get the Result as below
PSPS_Col1PS_Col2NPS1NPS1_Col1NPS1_Col2NPS2NPS2_Col1NPS2_Col2
ANULLNULLBNULLNULLCXYZ123
DAAA111ENULLNULLFNULLNULL
GNULLNULLHNULLNULLINULLNULL
JBBB222KNULLNULLLNULLNULL
By using case statement we can retrieve the data, but looking for alternate way to do it.
View 4 Replies
View Related
Feb 19, 2008
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100
101 Vol charity 200
101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'PRODUCT_CODE'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
Server: Msg 207, Level 16, State 1, Line 2
Invalid column name 'product_code'.
SELECTp.ID,
p.PRODUCT_CODE as Chapt,
p.product_code as Dues,
p.product_code as Vol
from (
SELECT ID,
product_code as Chapt,
Null as dues,
Null as Vol
from subscriptions
where prod_type = 'chapt'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
product_code as Dues,
Null as vol
from subscriptions
where prod_type = 'dues'
AND BALANCE > 0
union all
SELECT ID,
Null as chapt,
Null as dues,
product_code as Vol
from subscriptions
where prod_type = 'vol'
AND BALANCE > 0
) AS p
GROUP BY p.id
View 9 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
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
Mar 15, 2014
-- My first Data
create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')
[code]....
View 1 Replies
View Related
Mar 5, 2015
I have a lookup table, as below. Each triggercode can have several service codes.
TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC
I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.
AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3
What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.
I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.
What I would like to get is just the service codes and the appropriate trigger code for each account.
SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')
[Code] ....
View 1 Replies
View Related
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
View Related
Jun 2, 2014
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies
View Related
Jul 15, 2007
hi friends, i've a table with (columns) username, content,data,........... i need to delete all column names(i.e.,content,data,........) except username for the specified username. eg: consider username=mahendran. i've to delete the values in the content,data,...............for the username=mahendran. but username should exist.how to do that?pls help me......
View 3 Replies
View Related
Sep 8, 2014
I have two tables.
One called Boats with fields
IDSerialNo AreaCompany
There is another table called Equipment with fields
ID is the same in Boats as the ID in Equipment (Foreign Key).There can be multiple Parts linked to ID.I want to be able to return all the rows from Boats but only the Parts for each ID from Equipment. I dont want the other fields appearing more than once in the query.
View 4 Replies
View Related