Combine Data In Single Row
Apr 7, 2006
SELECT *
FROM dbo.empBenefits q
WHERE (StartDate IN
(SELECT TOP 2 STARTDATE
FROM EMPBENEFITS
WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON'
ORDER BY startdate ASC))
I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.
View 1 Replies
ADVERTISEMENT
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Nov 18, 2007
Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department: Sells:
1 Meat
1 Rice
1 Orange
2 Orange
2 Apple
3 Pears
The Data I would like read separated by Semi-colon:
Department: Sells:
1 Meat;Rice;Orange
2 Orange;Apple
3 Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
View 2 Replies
View Related
May 13, 2004
SELECT 1 as id,COUNT(name) as count1
INTO #temp1
FROM emp
SELECT 1 as id,COUNT(name) as count2
INTO #temp2
FROM emp
WHERE name <>' ' AND name IS NOT NULL OR name <> NULL
SELECT (cast(b.count2 as float)/cast(a.count1 as float))*100 AS per_non_null_names
FROM #temp1 a INNER JOIN #temp2 ON a.id=b.id
View 9 Replies
View Related
Mar 29, 2007
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View 13 Replies
View Related
Jun 5, 2014
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
[Code] .....
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
View 2 Replies
View Related
Jul 8, 2014
With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.
;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name
View 6 Replies
View Related
Jan 28, 2008
Hello:
I have the following table. There are eight section IDs in all. I want to return a single row for each product with the various section results that I have information on.
productID SectionID statusID
10 1 0
10 2 1
10 3 2
10 4 1
10 5 3
10 6 1
11 1 0
11 2 1
11 3 2
11 7 3
11 8 3
Need to return two rows with the respective values for each section.
productID section1 section2 section3 section4 section5 section6 section7 section8
10 0 1 2 1 3 1
11 0 1 2 3 3
Any information or if you can point me in the right direction would be appreciated.
Thanks
View 4 Replies
View Related
Dec 22, 2006
Hi,I'm working on a system migration and I need to combine data from multiplerows (with the same ID) into one comma separated string. This is how thedata is at the moment:Company_ID Material0x00C00000000053B86 Lead0x00C00000000053B86 Sulphur0x00C00000000053B86 ConcreteI need it in the following format:Company_ID Material0x00C00000000053B86 Lead, Sulphur, ConcreteThere is no definite number of materials per Company.I have read the part ofhttp://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'TheIterative Method' but my knowledge of SQL is very limited and I don't knowhow to use this code to get what I need.Can anyone help me?
View 7 Replies
View Related
Jul 20, 2005
Hi everyone,I really appreciate if anyone could help me with this tricky problemthat I'm having. I'm looking for a sample script to combine data inmultiple rows into one row. I'm using sqlserver. This is how data isstored in the table.ID Color111 Blue111 Yellow111 Pink111 GreenThis is the result that I would like to have.ID Color111 Blue, Yellow, Pink, GreenThere is no definite number of colors per ID. I have to use ID togroup these colors into one row. Therefore, ID becomes a unique keyin the table.Appreciate your help and time. Thank you in advance
View 1 Replies
View Related
May 24, 2015
How to combine multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID
The reult for the above query is:
RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1
I need the result like the below
RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46
View 2 Replies
View Related
Oct 8, 2007
Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888
And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888
where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?
View 1 Replies
View Related
Apr 12, 2006
Hey guys,
I realize I've posted something like this before, but i'm confused how to do something new, what I am trying to do is combine rows of data when certain columns equal one another.
For example, I have to sets of data who have in common the columns ctp_code and a mod_code (they are the same) however they have two other columns called billing_amt and amount_owed that need to be added together (literally taking the data and doing the math, so 100.00 + 100.00 = 200.00)
Code:
id ctp_code mod_code billing_amt amount_owed
1 1 1 100.00 100.00
2 1 1 100.00 100.00
My results should be this:
Code:
id ctp_code mod_code billing_amt amount_owed
1 1 1 200.00 200.00
Any help, thoughts?
View 10 Replies
View Related
Jul 1, 2014
I have two tables that I need to combine, they are as follows:
AccountCheckInDT
3289442/14/2014 15:11
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35
3289442/19/2014 9:41
AccountCheckOutDT
3289442/14/2014 16:18
3289442/16/2014 0:27
3289442/19/2014 9:28
3289442/19/2014 10:00
This is how I need the end result to look:
AccountCheckInDT CheckOutDT
3289442/14/2014 15:11 2/14/2014 16:18
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58 2/16/2014 0:27
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35 2/19/2014 9:28
3289442/19/2014 9:41 2/19/2014 10:0
View 3 Replies
View Related
Oct 3, 2006
I have a table Table, there are two set of Number and store in Tel1 and Tel2
I want to get all the number of each reacod that start with '1' on each record, the number I need that may in Tel1 or Tel2, I want to get the result like 'Result' table how can I do it ?
THX
TableA
+----+-------+-------+
| ID | Tel1 |Tel2 |
+----+-------+-------+
| 1 | 12223 | 92269 |
| 2 | 12269 | 97789 |
| 3 | 96636 | 13369 |
| 4 | 12259 | 97781 |
| 5 | 92889 | 12263 |
+----+-------+-------+
Result
+----+-------+
| ID | Tel |
+----+-------+
| 1 | 12223 |
| 2 | 12269 |
| 3 | 13369 |
| 4 | 12259 |
| 5 | 12263 |
+----+-------+
View 1 Replies
View Related
Oct 6, 2015
I have 2 tables. first table contains name and age and the second table contains class and roll num. Â I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.
View 8 Replies
View Related
Feb 5, 2015
I am running a query to pull data from 2 tables. However multiple data elements could be attached to one unique ID which when I run the query it repeats causing the entire data set to give inaccurate numbers. How to achieve this:
xxx.001A3264
xxx.001A3685
xxx.002A3261
xxx.002A3685
I would like my results to look like this:
xxx.001A3264 & A3685
xxx.002A3261 & A3685
View 2 Replies
View Related
Aug 20, 2007
I have extracted the data from Point of Sales. it has two columns one for date and one for time in the database.
I need to combine both column into single column with the format "mm/dd/yyyy hh:mm:ss" before i import the data into the SQL server for my BI project.
Example: Data extract from Point of Sales
FDATEFTIME
20060114063616
20060115070743
20060116071020
How can i combine those two when i import into SQL server like below:
FDATE
01/14/2006 06:36:16
01/15/2006 07:07:43
01/16/2006 07:10:20
million thanks.
View 2 Replies
View Related
Dec 17, 2013
I have a set of tables that look like what I have shown below. How I can achieve the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
[Code] ....
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
How it can be done ?
View 2 Replies
View Related
Aug 13, 2014
Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.
View 9 Replies
View Related
Aug 31, 2007
When quering a table with given criteria, For ex:
select notes, jobid, caller from contact where status in (6) and jobid = 173
I am getting this:
This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906
What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?
Please HELP!!!!!
View 4 Replies
View Related
Mar 28, 2008
I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.
View 10 Replies
View Related
Mar 16, 2006
I€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt
The txt file going from top to bottom is sort of grouped like this
Header Row (designated by €˜HD€™)
Corresponding Detail Rows for the Header Row
€¦..
Next Header Row
Corresponding Detail Rows
€¦and so on
http://www.webfound.net/rows.jpg
2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.
This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
The filenames of the txt files will vary and be based on one of the column values already in the header table.
Here is a print screen of my package so far:
http://www.webfound.net/tasks.jpg
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
http://www.webfound.net/DataFlow_Task_components.jpg
Let me know if you need more info. Examples of the actual data in the tables are here:
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg
View 17 Replies
View Related
Nov 17, 2006
Hi,
I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').
How do I handle in this case? Did any one come across such case?
Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?
If yes, how to do?
Thanks
mka
View 1 Replies
View Related
Nov 25, 2015
I have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
View 3 Replies
View Related
May 12, 2006
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:
"This operation conflicts with another pending operation on this transaction. The operation failed."
The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.
Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?
I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?
Thanks in advance.
View 3 Replies
View Related
Aug 4, 2006
Hi,
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
Many thanks
Rob Gibson
View 5 Replies
View Related
Jan 9, 2007
Hi,
In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error.
An error occurred while executing a command.
Message: Invalid object name 'Table2'.
Command:
SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model
FROM Table2) t
Is there any way to create a report with multiple data sources?
View 8 Replies
View Related
Apr 2, 2008
Hi all experters,
Please suggest how to build the report in below case:
Raw data:
ID
Member
Functions
1
Alan
A
1
Alan
B
2
Tom
A
2
Tom
B
2
Tom
C
3
Mary
D
3
Mary
E
Report Shows:
ID
Member
Functions
1
Alan
A,B
2
Tom
A,B,C
3
Mary
D,E
I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.
Any good suggestion?
Thanks in advance,
Steve Wang
2008/4/2
View 6 Replies
View Related
May 6, 2015
I'm trying to get all this data into a single row, but instead I'm getting results like a staircase with multiple rows. What concept am I missing here?
View 5 Replies
View Related
May 22, 2008
Im attempting to put data from an xml file (formatting like its different tables) into a database table as a single data row entry. I've attempted to use the 'union all' and 'merge' transformation but this inserts the data as multiple rows. Is this possible ?.
View 8 Replies
View Related
Feb 2, 2006
Hi,
I have a table such as
ID Name OS
----------------------------------
10 Paul AIX
10 Paul SOLARIS
10 Paul NT
20 Jack NT
20 Jack SOLARIS
and I have asked to create an output as
ID NAME OS
-----------------------------------
10 Paul AIX,SOLARIS,NT
20 JAck NT,SOLARIS
How can I get this output via sql.
Also a good source for such tricky SQLs would be very fruity.
View 5 Replies
View Related
Jun 23, 2008
Hello All,
I have following type of table with data.
Year id value
01-01-2008 123 10
01-01-2008 131 11
01-01-2008 139 132
01-01-2007 153 112
01-01-2007 154 134
01-01-2007 155 164
I want to write query such that I should get value of particular year in single row as follows
Date Col 1 Col 2 Col 3
01-01-2008 10 11 132
01-01-2007 112 134 164
How can I write this type of query ? Please do let me know if anyone know how to write it ?
Thanks in advance.
--kneel
View 5 Replies
View Related
