SQL Server 2012 :: Extracting Data From Row And Inserting Into Separate Columns In Different Tables?
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
ADVERTISEMENT
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Jul 14, 2015
SQL 2008R2
Request is to merge or join or case stmt or union or... from up to four unique columns all in separate tables to new combined table (matrix) of results from said.
What is example of best method to do this ?
View 2 Replies
View Related
Feb 15, 2012
I am trying to add 2 separate columns from separate tables i.e column1 should be added to column 2 when inserted and I want to use a trigger but i don't know the syntax to use...
View 14 Replies
View Related
Aug 20, 2014
I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.
My end result should look like the below example.
Item Location QtyOnHandByLocation SumQtyCommitTotal
Prod1 NJ 10 10
Prod1 NY 10 0
Prod1 FL 0 0
Prod1 PA 0 0
So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.
Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.
--create the temp table
Create table #SalesLine
(
Novarchar (50) not null
, LocationCodevarchar (50) not null
, QtyCommitint not null
)
create table #ItemLedgerEntry
[code]....
I am close to the desired results but can't find a way.
View 9 Replies
View Related
Jun 9, 2014
My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.
For this one I need to write 4 individual delete statements.
Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?
[URL] .....
I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error
View 2 Replies
View Related
Nov 13, 2014
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
View 5 Replies
View Related
Sep 28, 2015
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
[Code] ....
View 6 Replies
View Related
Aug 18, 2015
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018
The query I am using
select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'
View 3 Replies
View Related
Feb 25, 2014
I have a very simple query like the following…
SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)
From this query, its returning me a result set of something like below:
--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------
RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER
Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…
Something for example (I am not trying to write a condition: my question is how to write it),
if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.
Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…
See the attachment for better formatting...
---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH
View 7 Replies
View Related
Jun 12, 2014
I've a table as below
custid,companyname,phone,address
2,AAAAA,(222) 222-2222,address 2
3,cust 3,(333) 333-3333,address 3
5,BBBBB,(333) 333-3333,DDDDD
6,cust 6,(222) 222-2222,address 6
7,cust 7,(222) 222-2222,address 7
How to split csv values to new fields. so that the desired output should be as below
custidcompanynamephone address
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB (333) 333-3333 DDDDD
6 cust 6 (222) 222-2222 address 6
7 cust 7 (222) 222-2222 address 7
View 9 Replies
View Related
Aug 22, 2014
We have the below query that pulls benefit ids for employees but it will show each benefit on a separate row but we would like to have just one rows for the employee and columns for each of the benefits.
SELECT
hcd.PersonId,
hcd.PlanYear,
hcd.TaxIdNumber,
hcd.LastName,
hcd.FirstName,
hcd.BirthDate,
[code]....
View 3 Replies
View Related
Feb 25, 2015
I am trying to take the results of a query and re-orient them into separate columns.
select distinct
W_SUMMARYDETAILS.FACILITY_ID,
W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year)
W_SUMMARYDETAILS.FACILITY_NAME,
W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year)
From W_SUMMARYDETAILS
full outer join W_DEF_SUMMARYDETAILS
on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and
W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
View 9 Replies
View Related
Jun 19, 2014
I have two queries that give me the total sales amount for the current year, and the last year.
SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'
[Code] ....
I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.
This is my query without the columns I need:
SELECT
c.CustomerNumber
,c.Name
,c.ChainName
,c.PaymentTermsCode
,cle.CreditLimit AS 'CreditLimit'
,SUM(cle.Amount) AS 'Amount'
[Code] ....
View 1 Replies
View Related
Aug 18, 2014
Can you separate a domain attribute into two columns in the Excel Add-In?
We are using the Excel Add-In as the UI for business users and they want to see one of the domain attributes as 2 different columns.
The attribute is Store, currently it is displayed like this:
"123 {STORENAME }".
They want it displayed in two columns in the Excel document for easier filtering/sorting: Store Number and Store Name.
Is this possible? If one column is changed the other would change right along with it.
View 2 Replies
View Related
Jul 30, 2014
We have a large Datawarehouse and the size is 50TB.. The tables are placed in filegroups based on the schema like fact, dimensions, raw data each sit on seperate filegroups. I am thinking will it make sense to seperate the large facts which are having billions of rows so that they reside on filegroups on their own..
View 9 Replies
View Related
Nov 11, 2004
Hi all,
If I have a column in one table (contracts) that contains a set of values (codes that identify the fields contract code) like as follows...
Code:
Contracts table
--------------------------------
Concode - description
--------------------------------
KIDD - Kidderminster General
UNIV - University Hospitals
and then another table (controls) which has a column called contracts where the data within the 'Contracts' field is set out like the following (yes that is right, each of the data in this column are the Foreign Keys which are separated by '/' in which I need to query against the contracts table ) ...
Code:
controls table
--------------------------
Code - Contracts
--------------------------
BA - KIDD /UNIV /NWPCT
With the those tables and the columns like they are, how can I get my query to display the following information...
PHP Code:
---------------------------------------------------
Con(Controls table) - Description(contracts table)
---------------------------------------------------
BA - Kidderminster General
BA - University Hospitals
---------------------------------------------------
I'm guessing my first job is to somehow extract those Foreign Keys from the 'Contracts' column in the 'Control' table
Tryst
View 7 Replies
View Related
May 19, 2004
Cheers
I would really apprecite if anyone could make a comment on this...
what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'
Thanks
View 5 Replies
View Related
Nov 11, 2014
I have 2 tables in my database.
one is Race table and 2nd one is Age Range.
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
View 1 Replies
View Related
May 3, 2007
Hi @ll, I'm trying to extract data from a database where columns have names with spaces inside, I show an example:
select "total amount"
from sales;
This query shows me all data stored in the "total amount" column in table sales, ok no problem.
Well, the problem comes when I try to execute this query with osql because the output text file where I put the data only have the text "total amount".
I use the query as I wrote before stored in a text file.
I'm doing something like this:
osql -U user -P passwd -i query.sql -o textfile.txt
Can anybody give a hand? What I'm doing wrong?
Thank you in advance.
Bye.
View 2 Replies
View Related
Apr 14, 2014
Source System : Oracle
Target System : Ms Sql Server 2012
ETL Tool Used : SSIS
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
View 2 Replies
View Related
Nov 5, 2014
I have 1 table that is just a list of feeds. A, B, C, D etc (15 rows in total) and each feed has other information attached to it such as Full name, location etc etc. I am only interested in the Feed column.
Each feed then has a corresponding data table which contains a list of records. Eg Feed A data is contained in TableA, Feed B data is contains in TableB and so on.
Basically what I need is a combined table that shows the list of Feeds in the 1st Column ( So A, B, C, D…..) and then a second column which counts the records from each separate data table corresponding to that feed.
So the end result would look something like this:
Feed------No of Records
A----------4 (from TableA)
B----------7 (from TableB)
C----------8 (from TableC)
D----------1 (from TableD)
Possible?
View 2 Replies
View Related
Apr 29, 2015
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
View 6 Replies
View Related
Aug 18, 2014
I need extracting string that is between certain characters that are in certain position.
Here is the DDL:
DROP TABLE [dbo].[StoreNumberTest]
CREATE TABLE [dbo].[StoreNumberTest](
[StoreNumber] [varchar](50) NULL,
[StoreNumberParsed] [varchar](50) NULL)
INSERT INTO [dbo].[StoreNumberTest]
[Code] ....
What I need to accomplish is to extract the string that is between the third and fifth '-' (dash) and insert it into the StoreNumberParsed while eliminating the fourth dash.
Sample output would be:
KY117
CA132
OH174
MD163
FL191
I know that parse, charindex, patindex all might come in play, but not sure how to construct the statement.
View 5 Replies
View Related
Dec 12, 2013
Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.
Table schematics
di.DivisionRegion,int
P.PSIKey,int
P.PSIOutcome,int
P.PSIExpected,int
[Code] .....
View 2 Replies
View Related
Dec 15, 2014
Here's the resultset:
Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2
Expected result:
P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A
What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).
View 3 Replies
View Related
Jan 29, 2014
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0
View 4 Replies
View Related
Oct 28, 2015
I have the following results:
CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110
And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.
View 9 Replies
View Related
Mar 18, 2015
Has ever built a separate server just for SSIS and how did you do it?
View 2 Replies
View Related
Oct 1, 2014
I work in Healthcare IS for Company A, but Company B is hosting one of our EMR programs for us. This was done on purpose, so that whether a patient is seen at one or the other, their medical history is more complete. However, this puts all of the data that I need to get to on a server across town that I can only access via Sql Server Management Studio as a linked server.
Now, in some ways, the performance has been better than I expected, but sometimes it behaves very erratically. I am using OPENQUERY to handle all of the pulls, and am not joining to any local tables, in order to maximize efficiency.
Here is some of the code I run, and what happens:
SELECT *
FROM
OPENQUERY([linkedservername],
'
SELECT *
FROM Encounter_ItemChild
[Code] ....
***The above query was programmatically generated by taking the IDs from the second query, and packing as many into the IN condition as possible. Each statement could hold only about 900 IDs, so around 70 queries get built...however, each one returns the records in question in 1-2 seconds.
My main question is...if the second query pulls all IDs from Encounter in a few seconds, and that query is used in the first query's WHERE clause, why does it spin and spin, while manually throwing the IDs in instead runs almost instantly?
View 3 Replies
View Related
Dec 2, 2013
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
View 1 Replies
View Related
Feb 20, 2014
Is there a way to query all the columns in a database that have the data type as Integer.
View 9 Replies
View Related
Jul 29, 2015
any tricks to separate this column from:
"testlname,testfname comment section > comment section testlname,testfname 6/30/2015 9:05:58 AM > testlname,testfname 6/30/2015 9:06:18 AM > comment section testlname,testfname 6/30/2015 9:10:05 AM > comment section"
to:
col1 Col2 col3
testlname,testfname 6/30/2015 9:05:58 AM comment section
testlname,testfname 6/30/2015 9:06:18 AM comment section
testlname,testfname 6/30/2015 9:10:05 AM comment section
View 1 Replies
View Related
