Combine sql query from multiple returns to 1 return

the following is my query, what I would like to do is show 1 line for every person instead of  potentiall many.  Most of the information is redundent except for the dates and the count of the dates and mileage.  What I would like to do is combine all the dates into one field and do a count on them for 1 tally as well as the mileage.  Here is how the information come out currently per the query.
133RD       F31LFVXG      GASPERSZ       555555555      E3-6      3/28/2009      1      320
133RD       F31LFVXG      GASPERSZ        555555555      E3-6      3/29/2009      1      0
133RD       F31LFVXG      GASPERSZ        555555555      E3-6      3/30/2009      1      0
133RD       F31LFVXG      GASPERSZ       555555555      E3-6      3/31/2009      1      0
133RD       F31LFVXG      GASPERSZ       555555555      E3-6      4/1/2009      1      0
133RD       F31LFVXG      GASPERSZ        555555555      E3-6      4/3/2009      1      0
133RD       F31LFVXG      GASPERSZ     555555555      E3-6      4/4/2009      1      0
133RD       F31LFVXG      GASPERSZ       555555555      E3-6      4/5/2009      1      0
133RD       F31LFVXG      MENDENHALL       555555555      E4-1      4/6/2009      1      0
133RD       F31LFVXG      RICHTER       555555555      E3-1      4/3/2009      1      0
133RD       F31LFVXG      ZERWAS       555555555      E3-1      3/27/2009      1      311
133RD       F31LFVXG      ZERWAS        555555555      E3-1      3/28/2009      1      0
133RD       F31LFVXG      ZERWAS       555555555      E3-1      3/29/2009      1      0
133RD       F31LFVXG      ZERWAS       555555555      E3-1      3/30/2009      1      0
133RD       F31LFVXG      ZERWAS        555555555      E3-1      3/31/2009      1      0
133RD       F31LFVXG      ZERWAS      555555555      E3-1      4/1/2009      1      0

This is what I would like it to look like.
133RD       F31LFVXG      GASPERSZ        555555555      E3-6      3/28/2009, 3/29/2009, 3/30/2009, 3/31/2009, 4/1/2009, 4/2/2009, 4/3/2009, 4/4/2009, 4/5/2009      8      320
All the dates of that individual would be combined into one field and then counted to get the 8 and then the mileage sumed up as well,  So this goes from 8 records down to one combined record.

 

The following is my sql query.

select	u.strUnit, 
	u.strPASCODE, 
	s.strName, 
	p.strSSN, 
	s.strGrade + '-' + s.strYrsSVC as PGYrs, 
	p.dtAttendance, 
	count(p.dtattendance) as TotalDays, 
	sum(intMileage) as Miles 
from	tblMnAIrPersonnel as s INNER JOIN 
                tblMnAFUnits as u on u.strPascode = s.strPascode INNER JOIN 
	tblAssignedPersonnel as p on p.strSSN = s.strSSN 
where	p.bitPresent = 1 and intDrillStatus = 2 and dtPayProcessed IS Not Null and dtPaid is null and p.intUICID in (select intUICID from tblUIC where intTaskForceID = 7 and strUIC = 'F31LFVXG')
group by s.strName, p.strSSN, s.strGrade + '-' + s.strYrsSVC, u.strUnit, u.strPASCODE, p.dtAttendance

0
kldeutsch
4/16/2009 8:40:44 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

3 Replies
762 Views

Similar Articles

[PageSpeed] 49
Get it on Google Play
Get it on Apple App Store

This is a commom question in SQL forums. Instead of answering here, I am pointing you to a good resource provided by Arnie Rowland:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList&referringTitle=Home


Limno

0
limno
4/16/2009 9:25:16 PM

Hi, kldeutsch

 Try This Example

 

Create table Test_PivotNew
(col1 varchar(20),col2 varchar(20),col3 varchar(20),col4 varchar(20),col5 varchar(20),col6 datetime,col7 int,col8 int)
Go
insert into Test_PivotNew
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '3/28/2009' ,     1  ,    320
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '3/29/2009' ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '3/30/2009' ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '3/31/2009'  ,    1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '4/1/2009'  ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '4/3/2009'  ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '4/4/2009'  ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'GASPERSZ'  ,    '555555555',      'E3-6'  ,    '4/5/2009'  ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'MENDENHALL',    '555555555',      'E4-1'  ,    '4/6/2009'  ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'RICHTER'   ,    '555555555',      'E3-1'  ,    '4/3/2009'  ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'ZERWAS'    ,    '555555555',      'E3-1'  ,    '3/27/2009' ,     1  ,    311
union all
Select '133RD'    ,   'F31LFVXG'  ,    'ZERWAS'    ,    '555555555',      'E3-1'  ,    '3/28/2009' ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'ZERWAS'    ,    '555555555',      'E3-1'  ,    '3/29/2009' ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'ZERWAS'    ,    '555555555',      'E3-1'  ,    '3/30/2009' ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'ZERWAS'    ,    '555555555',      'E3-1'  ,    '3/31/2009' ,     1  ,    0
union all
Select '133RD'    ,   'F31LFVXG'  ,    'ZERWAS'    ,    '555555555',      'E3-1'  ,    '4/1/2009'  ,     1  ,    0
Go


SELECT   col1,col2,col3,col4,col5,
(stuff(
(
Select ',' + convert(varchar,emp.col6,101)
From Test_PivotNew emp
WHERE emp.col1 = empnew.col1
Order by col1,col2,col3
FOR XML Path ('')), 1, 1, '')) as col6,
count(col7) as count,Sum(col8) as [tot]
FROM Test_PivotNew empnew 
group by col1,col2,col3,col4,col5


Go

--Output
col1                 col2                 col3                 col4                 col5                 col6                                                                                                                                                                                                                                                             count       tot
-------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
133RD                F31LFVXG             GASPERSZ             555555555            E3-6                 03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009,04/03/2009,04/04/2009,04/05/2009,04/06/2009,04/03/2009,03/27/2009,03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009                                                                                  8           320
133RD                F31LFVXG             MENDENHALL           555555555            E4-1                 03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009,04/03/2009,04/04/2009,04/05/2009,04/06/2009,04/03/2009,03/27/2009,03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009                                                                                  1           0
133RD                F31LFVXG             RICHTER              555555555            E3-1                 03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009,04/03/2009,04/04/2009,04/05/2009,04/06/2009,04/03/2009,03/27/2009,03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009                                                                                  1           0
133RD                F31LFVXG             ZERWAS               555555555            E3-1                 03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009,04/03/2009,04/04/2009,04/05/2009,04/06/2009,04/03/2009,03/27/2009,03/28/2009,03/29/2009,03/30/2009,03/31/2009,04/01/2009                                                                                  6           311
  
RAGHAV

MVP ASP/ASP.Net Read My Blog


MARK THE POST AS ANSWER IF IT HELPS U.


"Success doesn't come to you…you go to it."--Marva Collins




"Success does not come to those who wait . . . and it does not wait for anyone to come to it." Anonymous


0
raghav_khunger
4/17/2009 5:14:25 AM

try this format

SELECT DISTINCT col+ ',' AS [text()]

FROM tbl

FOR XML PATH('')

0
mo
4/17/2009 5:28:40 AM
Reply:

Similar Artilces:

SQL query returns data, .NET returns #42S22Unknown column binding to dataset
I have a webservice with a common function which calls MYSQL and returns a dataset. Today I encounterd a strange error when performing a Select with a count() in it which I cant figure out. From a command line, the SQL query works fine and returns 9 rows... but VB.NET bombs with an error when filling the data adapter with the returned data. Please see my sample code below, is there an additional flag I need to call to make this work with VB? SQL Command Line: ( Works Great )mysql> Select taction, count(*) from kiosklogs where packageid='NewHi-Def-Test' group by taction;...

Combine multiple SQL queries in 1 datagrid
Is there a way to combine multiple queries in 1 datagrid? I have several filters and evaluations that apparently can't be performed in the same query, but I need the results all displayed in one datagrid. Can you use a stored procedure for your queries? Yup Check this out : http://support.microsoft.com/default.aspx?scid=kb;en-us;325688&Product=adonet Not sure if that was your question... Hope this helps... - dip That looks like it might work. Thanks. I ended up doing it in an array and loading everything in a data grid. Thanks. Can you share some of the synta...

sqlreader returning false even though Sql query Analyzer returns a result
Hi, I have this problem with my sqlreader, my sql returns a result in analyzer but when I use the same statement in my sqlreader its not returning any results ie returns false.   Can anyone help?   many thanks Hi,1. Are you sure it's EXACTLY the same SQL statement executing on both?2. Are you sure they are both pointing to the same database?If so, post the code and we'll take a look. ...

Help with DISTINCT return in SQL query with multiple selections
I'm exporting the following query to a datagrid, however in the result set, some values are duplicated (for various reasons... mostly old software and poor categorization)...On the records with identical values, I want to look at the account number and the DateOfService fields and search for joint distinct values and only display that...Current Example:  ACCT NUM   |  DATE OF SERVICE  |________________________________   43490          |     10/01/2006  08:15:23  | ...

Carriage returns with returned SQL data
In PB10 is it possible to create a single computed field the takes values returned by an SQL query and apply a carriage return function to put each returned value onto its own line. I have to program a datawindow that prints an address, but the address has to be formatted differently depending on whether a) there is an address line 2, at which point the city, state, zip all move up 1 line or b)if the address has to meet a different layout for international users (eg Germany where the zip comes before the city). Sure. I'd separate the address elements into different columns, use S...

Avoiding changing the contents of DetailsView & GridView if an SQL query from DataSource returns no data
 Hi All,I'm fairly new to asp.net programming and especially the data viewing forms. I am trying to debug a page containing DetailsView and GridView controls bound to a ms access db. The page also has a search bar with some options to allow the user to search for articles in the db. The problem is, if a search returns no results, the DetailsView control goes from its ReadOnly mode to Insert mode where I can input information for a new entry into the database. Also, the GridView control on the page disappears. Are there any tutorials or posts that could help me in finding out what wo...

Why does a simple select query in Microsoft Access return the error [Intersolv][ODBC SQL Server Driver][SQL Server]ct_results(): user api layer: internal client library error: Read from the server has
I have applied the information from support document 10447954 but that does not solve the problem. I am running a simple select query with one criteria from one table. Thanks. ...

Why does a simple select query in MS Access return the error: [Intersolv][ODBC SQL Server Driver][SQL Server]ct_results(): user api layer: internal client library error: Read from the server has timed
I have applied the information contained in support document 10447954. That did not resolve the problem. I am running a simple query with one criteria and one table Thanks. Hi Bill, The doc refers to a different ODBC driver (for DirectConnect product) so you might have a completely different problem. The message implies you are using ODBC 12.0 (Client Library based) or prior since the message indicates CtLib info. You might want to post more details involved with getting to this error. You also might want to post exact versions of ASE and ODBC driver, including Open ...

sql or my sql query
hi,in my database  i have the two tables...1)programs                                2)reservedin the programs...two fields 1)program id           2)maxseats................these details wil be entered by adminin the reserved table  two fileds ..1)PRogramid        2)statusmy requirement is .. i need the available seats..meansif any body is intersted in  any program...they wil select the program...then status becomes approved(1)..then i need the o/p as ...

Return -1 vs. return (-1)
I've come across code from another programmer in which he uses parens to enclose the argument to RETURN: RETURN (-1) I usually just do: RETURN -1 Are these exactly equivalent? I've never seen that but would assume it is the same. Just like you I always return -1, or more correctly. return li_rtn -- Larry Cermak [Team Sybase] Branick Consulting, Inc. www.branick-inc.com EAServer & PowerBuilder Consulting & Training EAServer and PowerBuilder Computer Based Training Courses "Rick Charnes" <rickxyz--nospam.zyxcharnes@yahoo.com>...

SQL query returned in label
Hey guys. New to the forums but I have enjoyed reading so far.  My first question for today is... I have a label on my default page that I want to display the results of a table adapter. My label is labelTotals My table adapter is tblLicenseCountAdapter Can someone help me out please? Is your sql query returning a single value??Remeber to mark as Answer if someone's answer helped you. Yes it is. Im actually just counting the rows for the value. Duplicate post for some odd reason. if you are just counting rows then are you looking for something like lblOutput....

How to return to perl from SQL query?
--0-2009163349-1090597920=:50908 Content-Type: text/plain; charset=us-ascii How can I run a sql script in Oracle and assure that the query won't hang up in the Oracle database and yet allow me to fetch data? I am trying several methods, but its still failing. Please advise. JC --0-2009163349-1090597920=:50908-- ...

SQL IF condition RETURN TRUE ELSE RETURN FALSE
I'm having problems trying to get the above statement to work. I'm performing an inner join to another table, then showing whether a record was returned or not. I'm not interested in the data of the record, I just want to know if it exists... Anyway, this is what I'm doing: "SELECT (IF (a.id IS NULL) FALSE ELSE RETURN TRUE) AS is_true..." Is this the incorrect use of the sql IF statement, I checked on MSDN and it seems to be used for scripting purposes?   Any help would be greatly appreciated. Thanks. select case when id is null then false els...

Can sql queries be nested in sql queries?
I am having problems with a template that uses the information from one sql query to generate a query on another table. Let me be more specific. The template in question receives a category ID value from a web page that provides a menu of several categories to choose from. Each Category ID represents several Items or products to choose from. When the template receives the category ID, the first thing it does is run a query on my item_category table (this table lists all of the items and associates them with the appropriate category). This query returns several itemIDs that are all...

Web resources about - Combine sql query from multiple returns to 1 return - asp.net.sql-datasource

Multiple sequence alignment - Wikipedia, the free encyclopedia
A multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...

Nigeria: Over 70 Feared Killed in Borno, Adamawa Multiple Blasts
More than 70 people were feared killed in multiple bomb attacks believed to have been carried out by Boko Haram militants and their agents in ...

Facebook is testing multiple topic-based News Feeds for mobile devices
... the information you want to see at the appropriate times. According to the screenshots taken by Jason Stein, Facebook looks to be testing multiple, ...

Multiple Jobholders Artificially Boost "Full-Time" Employment: Does the Sum of the Parts Equal the Whole ...
... pretty much confirms what I have said about Obamacare boosting part-time employment at the expense of full time jobs. Please consider Multiple ...

Munich Police: Islamic State Planned New Year’s Eve Attack with Multiple Suicide Bombers
Munich police warned of a “serious, imminent threat” by Islamic State group suicide bombers wanting to commit an attack on New Year’s Eve.

Jim Cramer on balancing multiple jobs, his Brooklyn bar, and the Jon Stewart interview - Business Insider ...
It was during law school that Cramer started recommending and trading stocks, which led to becoming a stockbroker at Goldman Sachs.

Miami teen shot multiple times at grandma's home
The victim reportedly remains in the hospital as police continue to search for the suspect

Watch Hero Philly Cop Get Shot Multiple Times And Then Give Chase
Watch Hero Philly Cop Get Shot Multiple Times And Then Give Chase

Showtime Previewing ‘Shameless’ & ‘Billions’ Premieres On Multiple Platforms
Demonstrating the increasing importance of the network’s original programming in bringing in new customers, Showtime will be providing subscribers ...

Facebook tests multiple News Feeds based on your interests
... yearning for a way to cut through the clutter that is your Facebook News Feed, you may soon be in luck. The social network is testing multiple ...

Resources last updated: 1/9/2016 1:19:55 PM