I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
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,'')
I came across this structure today and haven't seen it before:
SELECT blablabla FROM T1 FULL OUTER JOIN T2 ON T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 ON T3.Col1 = T1.Col1 AND T3.Col2 = T1.Col2 ON T4.Col1 = T1.Col1 AND T4.Col2 = T1.Col2
i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.
SELECT Distinct a.Col1 AS EmpID, a.Col1 AS EmpID, a.Col2 AS Emp_guid, a.Col2 AS Emp_guid, case
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns FROM table WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1') AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N', p.' + QUOTENAME(deck) FROM (SELECT p.deck FROM dbo.report AS p GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
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 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
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT b.[PBCat] ,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure ,a.[Station] ,a.[Facility] ,a.[CC] ,a.[Office]
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.
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
I think I need to only create few if an index is covering all columns then I do not need to create more indexes for separate columns or should I create separate index as suggested?
Similarly:
CREATE INDEX [NCIX_20187_20186_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([SerialNo],[StationaryStatus]) GO CREATE INDEX [NCIX_20189_20188_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([SerialNo]) GO
[Code] ....
Should I create all indexes above or use minimum number of indexes which covers all columns as mentioned in above create index statements?
Say you have a fact table with a few columns that all reference the same key column in a dimension table, you want to write a view to return the information for those keys?
USE MyTestDB; GO SET NOCOUNT ON; IF OBJECT_ID ('dbo.FactTemp' ,'U') IS NOT NULL DROP TABLE dbo.FactTemp;
[Code] ....
I'm using very small data at the moment, and the query plan and statistics don't really say which way.
These two T-SQL statements return the same results.
If Microsoft deemed it necessary to add the EXCEPT command, then what are its advantages over an INNER JOIN
-- LIST ONLY PRODUCTS THAT ARE ON A WORK ORDER
USE AdventureWorks2008R2; GO SELECT ProductID FROM Production.Product INTERSECT SELECT ProductID FROM Production.WorkOrder ;
USE AdventureWorks2008R2; GO SELECT DISTINCT Production.WorkOrder.ProductID FROM Production.Product INNER JOIN Production.WorkOrder ON Production.WorkOrder.ProductID = Production.Product.ProductID
What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear FROM tblCall INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
We have a query that joins column A int which is an int onto column B with contains only int's but was created as a varchar and can't be changed to an int at the moment.
Casting column a as a varchar in the ON of the join to left join seems to void the index altogether and the query just runs for every.
We are talking a few hundred million rows of data in each table.
Temp solution is select into a #Hash table as correct data type and index then use the #Hash table in the join.
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like - JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK) ON User_Count_Id = Analyst_Id_fk WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk )
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.
Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.
What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?
--- First Create two tables Create table tabA (cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100)) Insert into tabA values (NULL,'A1','A2') Select * from tabA create table tabB
