T-SQL (SS2K8) :: Combining Multiple Rows Into One Row Per Employee

Apr 3, 2014

I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.

Sample Data:

WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;

The results from the above are as follows:

PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate

The desired results would be:

PERSONDate Department Rate
123404/02/2014 123 80.0

View 7 Replies


ADVERTISEMENT

I'm Trying To Get The Last Activity Date From W/in A Table Where There Are Multiple Rows Per Employee

Apr 22, 2008

Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table
and I'm obviously not having much luck

This is basically the table layout, note each employee can have multiple rows with different dates






Employee_ID

Last_Name

First_Name

Evaluation_Date

Evaluation_Score


1

Jones

Tom

01/04/07

40


1

Jones

Tom

01/.12/07

50


1

Jones

Tom

04/01/08

60


2

Smith

Ed

02/14/05

70


2

Smith

Ed

03/18/06

80


3

Brown

John

06/23/04

80


3

Brown

John

12/23/04

79


3

Brown

John

01/07/06

50


3

Brown

John

10/22/08

69


What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table






Employee_ID

Last_Name

First_Name

Evaluation_Date

Evaluation_Score


1

Jones

Tom

04/01/08

60


2

Smith

Ed

03/18/06

80


3

Brown

John

10/22/08

69


I've looked at select distinct and the date operatives with out any success.

Thanks Much
Vince

View 3 Replies View Related

Combining Multiple Rows Into One

Feb 13, 2007

I have data that looks like this:

ID Value
1 Descr1
1 Descr2
1 Descr3

where Descr could range from 1 to 100 for each ID

The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you

View 8 Replies View Related

Combining Multiple Rows Into One

May 16, 2008

Hi,

I'm trying to do this in T-SQL. I have a query that returns, for this matter, only 2 rows. The table output looks like this:










AdjType
AdjNbrStart
AdjNbrEnd
AdjTotalAmt

1
9
9
-134180

2
8
10
104981.42

How do you do it to bring back only one row that will look like so:











1
9
9
-134180
2
8
10
104981.4


Is it possible? Like I said, I only have 2 rows. I'm not concatenating these columns, just want to bring it back as one row. Any ideas? Thanks.

View 1 Replies View Related

Combining Multiple Rows Into 1 Row X 1 Column

Jul 30, 2007

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?







Employee Name

John

Mike

Dale

View 5 Replies View Related

Combining Multiple Rows Based Off Criteria

Mar 21, 2006

Hello again,

Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:


Code:


Table A

ID DSN VN AX Diag
1111296.54
3212318.00



Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.


Code:


Table B

DSN Patient_id
100000001
200000001



So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.


Code:


Table A

ID DSN VN AX Diag
1111296.54
3112318.00

Note: The second rows DSN changed to 1 from 2




Code:


Table B

DSN Patient_id
100000001
(Duplicate row removed with same patient_id)



The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.

Table A:

DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)

Table B:

Second row with same DSN must be removed.

Any takes, ideas? I need to do this on a couple thousand rows....

Thanks, and im happy to clarify if needed.

View 1 Replies View Related

SQL Server 2012 :: Combining Multiple Rows Into One

Feb 4, 2015

How I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together.

I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.

00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000

[Code] ....

View 7 Replies View Related

Multiple Rows Into A Single Row And Combining Column Values?

Apr 6, 2014

I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.

Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.

EXAMPLE: All in one row.

ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.

I have attached an image of the SQL VIEW that I need to modified so it does this.

THE QUERY:

SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC

View 5 Replies View Related

SQL Server 2012 :: Combining Multiple Rows Into One Field

Feb 26, 2015

I am needing to combine the Notes field where Number and date are the same...For example

for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.

CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,

[Code] .....

View 9 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

Help With Combining Data From Multiple Rows Into One Column In A View

Jul 19, 2007

Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300

View 1 Replies View Related

T-SQL (SS2K8) :: Updating Multiple Rows

Jul 2, 2014

I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'

And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned

UPDATE sy01200
SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp')
WHERE Master_Type = 'EMP'

View 2 Replies View Related

T-SQL (SS2K8) :: Multiple Rows Into A Single Row

Nov 7, 2014

I am working with some old code that we are trying to clean up and perform some performance enhancements. The performance is now, so Very much better. From over 3 minutes to under 2 seconds.

But I am still trying to get the multiple rows into a single row. I would like to place this into a CTE to get the multiples into a single row. I just cannot get my head around how is the best, most efficient way to write the query.

This is a small example of what the rows look like in the resultset, and what I want to single to be.

DECLARE @BillingCorrect TABLE
(
ContractNumber char(10)
, pc1 int
, pb int
, om int
, vp int

[Code] ....

I am not sure how to write the query to have all the data in a single row.

View 2 Replies View Related

T-SQL (SS2K8) :: Splitting Row Into Multiple Rows

May 14, 2015

I have a table that has for example data

I am looking to write a script that will change the first table into the second table.......

Table 1

Account No Name other field
1 Mr T and Mrs M Lambert xxx

I need to rewrite this as

Table 2

Account No split Name other field
1 a Mr T Lambert xxx
1 b Mrs M Lambert xxx

View 9 Replies View Related

T-SQL (SS2K8) :: Merge Multiple Rows In Single Row

Jul 8, 2015

I've a requirement where I need to merge multiple rows in single rows. For example in the attached image output, I need to return a single column for type Case like this.

CH0, CH1, CH2, CHX Case
CM0, CM1, CM2, CMX Mechanical

I'm using T-SQL to generate the column type. Below is my DDL.

USE tempdb
GO
CREATE TABLE ProdCodes
(Prefix char(8),
Code char(5)

[Code] ....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Select All Or Multiple Rows From Typed XML Variable

Apr 2, 2014

I am using xml schema that is like this:

<DetailRows>
<DetailRow>
<MonthNumber></MonthNumber>
<Amount></Amount>
</DetailRow>
</DetailRows>If my variable contains following xml document as un-typed xml

[Code] ....

However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.

View 1 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows And Columns Into Single Row

Apr 25, 2014

I have this query

SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code

and the output

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10

How can I modify the query to come up with output below,

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10

View 9 Replies View Related

T-SQL (SS2K8) :: Fetch Next From Cursor Returns Multiple Rows?

Aug 9, 2014

I don't understand using a dynamic cursor.

Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.

Now the Skript to reproduce the whole thing.

create database objects

-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;

[Code]....

Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.

View 8 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows In Single Column

Sep 4, 2014

Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.

Below is sample of data:

create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')

Required output

depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2

View 2 Replies View Related

T-SQL (SS2K8) :: Find Working Time Of Each Employee

Aug 18, 2015

IF EXISTS ( SELECT TOP 1 1 FROM sys.tables WHERE name = '#EmpHours' )
DROP TABLE #EmpHours;
CREATE TABLE #EmpHours

[Code]....

View 7 Replies View Related

T-SQL (SS2K8) :: Send Multiple Rows With One HTML Table In Email?

Dec 2, 2014

I have a job below, which takes the results and send to the users in email.But I have a question, how can I send only one email with all rows, not to send the for every row on table separated email.

DECLARE @Body VARCHAR(MAX)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @RowCountINT
DECLARE@idINT
declare@departmentnvarchar(30)

[code]....

View 2 Replies View Related

How To Insert Employee Record Along With Multiple Email Ids Into The Sql Database?

May 3, 2008

Hello Everyone,
I am bit confused.
I am using vwd2005 express,c# and sql express.
I have a webform that registers new employee(title,name,age,address,phone,email) and inserts those data into the sqldatabase.
The problem is there might exists 'N' number or email ids and phone nos for a single user.
for eg: user A might have 3 email address where has user B might have 5 email address.
so in that case its not appropriate to create 5 textboxes in the webform and create 5 column like email1,email2,email3,email4,email5 in the database for both users..
i hope your getting the point.
I tried to create a separate table for emails and phone.
But i am new to relational database.
So if i need to use Relational database then could anyone help me here to :-
1.create a table structure here.(what would be the structure of tbl_employee, tbl_email and tbl_phone)
2.How to write insert query if i am using RD here?
plz help explaining the concept with a simple running example.
Thanks in advance.
Jack.
 
 
 
 

View 8 Replies View Related

Transact SQL :: How To Write Script For Employee Multiple Punch In

Aug 20, 2015

CREATE TABLE [dbo].[multiple](
[emp_code] [varchar](20) NULL,
[swip_in] [nvarchar](255) NULL,
[swipe_in_date] [nvarchar](255) NULL,
[swipe_out] [nvarchar](255) NULL,
[Swipe_out_date] [nvarchar](255) NULL

[Code] ....

I want result like below:
Empcode swip_in1,   swip_out1,    swip_in2,   swip_out2,   swip_in3,   swip_out3

Condition :
1. 24 hrs who are multiple time punched 

View 7 Replies View Related

T-SQL (SS2K8) :: Combining Result In Column Level

May 14, 2014

Below is my sample table and data

With Item as(
Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all
Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all
Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all
Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all
Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all
Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all
Select 7 as ItemId,'ItemName7' as ItemName,700 as position),

Mapping as (
Select 1 as Parent, 2 as child union all
Select 1 as Parent, 3 as child union all
Select 1 as Parent, 4 as child union all
Select 5 as Parent, 6 as child union all
Select 5 as Parent, 7 as child )Expected Result:

ParentItemIdParentItemNameParentpositionChildItemIdChildItemNameChildposition
1ItemName11002ItemName2200
1ItemName11003ItemName3300
1ItemName11004ItemName4400
5ItemName55006ItemName6600
5ItemName55007ItemName7700

I was thinking to achieve using union all but if i use union all it will combine the result in rows level. but i need in column level.

View 5 Replies View Related

T-SQL (SS2K8) :: Combining Keys To Create A Master List?

Mar 5, 2014

I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:

Original Data
key1 key2
647942600014
647942285437
2324662490640
2324662285437
2324662066128
2222 2285437
2222 1111111
9999 1111111
9999 2222222

Should look like:

22222600014
22222285437
22222490640
22222066128
22221111111
22222222222

I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.

I tried playing with self joins between the tables but have had no success.

I am using Sql Server 2008 and the number of records could 500K to 1MM.

View 2 Replies View Related

T-SQL (SS2K8) :: Combining Costs From Across Tables To Populate Temporary Table?

Nov 17, 2014

I have a temporary table (@tblResults) that has 4 columns that need to be populated with a calculation made from columns held within 2 other tables.

Joins
@tblResults tr JOIN dbo.MarketPrice mp
ON tr.Item = mp.Item
AND tr.[Month] = mp.[Month]
AND tr.[Year] = mp.[Year]
AND mp.[Date] BETWEEN tr.LatestStartDate AND tr.PriorEndDate

[code]....

Where the 2 dbo.MarketPrice and dbo.MillDifferentials date fields are NOT equal, the last (chronologically) dbo.MillDifferentials.Diff value should be used (or '0' if no previous value found).

so expected results where @tblResults.Id = 1:

The dbo.MarketPrice.Price value of '2014-10-29' should be combined with the dbo.MillDifferentials.Diff value of '2014-10-06' - this produces the combined Max value of 184.50
The dbo.MarketPrice.Price value of '2014-04-28' should be combined with the dbo.MillDifferentials.Diff value of '2014-04-28'
The dbo.MarketPrice.Price value of '2014-01-22' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20'
The dbo.MarketPrice.Price value of '2014-01-21' should be combined with the dbo.MillDifferentials.Diff value of '2014-01-20' - this produces the combined Min value of 111.50
The dbo.MarketPrice.Price value of '2014-01-04' should be combined with '0.00' if there is no matching or previous dbo.MillDifferentials.Diff value OR the top 1/max (most recent) dbo.MillDifferentials.Diff value if a record is found before the specified @tblResults.LatestStartDate

View 3 Replies View Related

Combining Two Rows Into One

Jul 23, 2014

I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.

Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355

View 2 Replies View Related

SQL Combining Duplicate Rows, Please HELP.

Feb 1, 2008

Hello,
I have a question, what does a statement look like that finds the duplicate rows and combines them,
I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number.
I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine.
Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish.
I have the following SQL statement:
SELECT part_number,
COUNT(part_number) AS NumOccurrences
FROM Products
GROUP BY Part_number
HAVING COUNT(part_number) > 1
 

View 7 Replies View Related

HELP - Combining Rows In A View

Jul 21, 2004

Hi All,

I can do this in Access, with VB, but I'm pretty new to SQL Server.

Say you have the following table, call it TblStudents:

Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony

And you wanted a view that would give you:

Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony

How would you do this in SQL Server?

Thanks.

Henry

View 1 Replies View Related

Combining Rows In A Table(again)

Jan 18, 2006

I've seen a number of questions on combining rows, but not one
exactly like this. I have a solution, but I'd like to know
if there are other ways.
I'd like to select and combine rows from a table. Here's a simplified
version of the table:
tab1
key date status
1 1/1/06 stat1
1 1/2/06 stat2
1 1/3/06 stat3
1 1/4/06 stat4
2 1/1/06 stat1
2 1/2/06 stat2

And the desired results:
key date status prevstatus
1 1/1/06 stat1 null
1 1/2/06 stat2 stat1
1 1/3/06 stat3 stat2
1 1/4/06 stat4 stat3
2 1/1/06 stat1 null
2 1/2/06 stat2 stat1

Here's the simplified version of the solution:
select
a.*,b.status prevstatus
from
tab1 a
left join
tab1 b
on a.key = b.key and
b.date =
(select max(date) from tab1 c
where
a.key = c.key and
a.date > c.date
)

Is there a better way?

View 5 Replies View Related

Combining Like Rows If More Than One UserID In The List

Jun 6, 2014

I have the following query:

Code:
SELECT DISTINCT [WL].[Id]
,[WL].[UserId]
,[WL].[DIF]
,[WL].[MW]
,[WL].[Notes]
,[WL].[WDate]

[Code] ....

And the error i get is:

> Column 'Wsite.dbo.WLog.Id' is invalid in the select
> list because it is not contained in either an aggregate function or
> the GROUP BY clause.

What I am wanting to do is just conbine the data if there are more than one **UserID** in the list.

As an example:

Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 14 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5845| 99304 | .... | 99304| Bob Barker | 8 | ....
5846| 99304 | .... | 99304| Bob Barker | 11 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....

So it should look like this:

Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 33 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....

Notice that Bob Barker's SLength was combined (14+8+11=33).

View 1 Replies View Related

Select Statement Combining Rows

Mar 30, 2008

I need some help with a query . I have two tables "config" and "item".

I have table config as follows:
ConfigID
ItemID

With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112

And table item as follows:
ItemID
ItemTypeID
ItemValue

With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789

So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID

Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL

How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC

Thanks for your help

View 6 Replies View Related

Combining Text Data Rows

Jun 4, 2006

I am working with a database derived from text documents. One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column. I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result). How do I combine the rows with the same DocumentID into a single row of text data? This will put the entire text content of each document in its own row.

TEXT001 table as it is





DocumentID

SectionNo

SectionText


1

1

Paragraph 1 of Document 1


1

2

Paragraph 2 of Document 1


1

3

Paragraph 3 of Document 1


2

1

Paragraph 1 of Document 2


2

2

Paragraph 2 of Document 2

New TEXT table





DocumentID

SectionText


1

Entire text of Document 1


2

Entire text of Document 2

I realize that I can use €œunion€? to combine tables with the same data type, but that is not what I am trying to do. Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command. If anyone can tell how to do this, I would appreciate your help.

More modestly, I tried to use the €œGroup By€? clause to combine the SectionText data using this query:

SELECT DocumentID, SectionText FROM TEXT001
GROUP BY DocumentID

And got this error message:

Msg 8120, Level 16, State 1, Line 5
Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be €œsummed€?, so I tried including it in the GROUP BY clause:

SELECT DocumentID, SectionText FROM TEXT001
GROUP BY DocumentID, SectionText

And got his error message:

Msg 306, Level 16, State 2, Line 5
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?

View 17 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved