MS SQL Server :: Multiple Rows Of Data Into 1 Row Results?

Multiple Rows To One Column Results

Nov 8, 2006

I want to display the top 5 count of areacodes for each name
I
want to combine all the results for the areacodes into one column for
each user. like a csv. I tried my code below but the results just
return the same 5 of areacodes for all names on each area code row with
each callername. like
joe blow 123,456,755,312,465,567,555
bill jones 123,456,755,312,465,567,555

I just want the top 5 for each particular name.
I tried reading a few articles on putting multiple colums in one row and i could not figure out what i am missing!
Thanks

DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), CallerName VARCHAR(256), AreaCode VARCHAR(3), Calls INT, theDate DATETIME,myareacodes int)
declare @MyAreaCodes varchar(50)
INSERT @Stage
(
CallerName,
AreaCode,
Calls,
theDate
--myAreacodes

) --This is where the area code data comes from I can get it to display in multiple colums, but -- I want the area codes to be on one line for each name
SELECT CallerName,
SUBSTRING(TargetNum, 2, 3) AS AreaCode,
COUNT(*) AS AreaCodeCount,
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0) as myDate
FROM CDRMAIN
WHERE LEN(TargetNum) >= 11
AND TargetNum NOT LIKE '_800%'
AND GtCalcDate >= '2006-11-06'
AND GtCalcDate < '2006-11-07'
GROUP BY CallerName,
SUBSTRING(TargetNum, 2, 3),
DATEADD(day, DATEDIFF(day, 0, GtCalcDate), 0)
ORDER BY CallerName,
COUNT(*) DESC

-- Get Calls
SELECT s.CallerName,
s.AreaCode,
s.Calls,
s.theDate--,myareacodes
FROM @Stage s
INNER JOIN (
SELECT CallerName,
MIN(RowID) mirw,
4 + MIN(RowID) marw
FROM @Stage
GROUP BY CallerName

HAVING (CallerName = 'name1') OR
(CallerName = 'name2')
) q ON q.CallerName = s.CallerName AND s.RowID BETWEEN q.mirw AND q.marw
ORDER BY callername,Calls desc
--
set @MyAreaCodes =''
--
SELECT top 5 @MyAreaCodes = @MyAreaCodes + ISNULL(AreaCode,'') + ',' from @Stage

--
SELECT CallerNAme,@MyAreaCodes AS MyAreaCodes from @stage
Group By CallerName

View 1 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

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

View 8 Replies View Related

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

Apr 23, 2008

Hello All,

I am rather new to reporting on SQL Server 2005 so please be patient with me.

I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.

For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report

The table (System Info) looks like:-

ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |

Which

So basically I need it to look like this.

Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy

So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1

How would I go about displaying the other system parts as columns with info

Any help is much appreciated!

View 3 Replies View Related

Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?

Nov 30, 2007

I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.

each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.

Ideas?
Thanks,
Ad.

View 7 Replies View Related

Insert Data Into Multiple Rows From ASP.net

Nov 3, 2005

I am stuck. I have some vars being passed to an aspx page that I need to dump into a db table in multiple rows, how do I do it? I am going into a SQL database using VS.Net 2003 here's the format that the vars come in the page as: UserID = 46 k12SessionArray0interaction_id = Interaction_01 k12SessionArray0correct_response = d k12SessionArray0student_response = d k12SessionArray0result = C k12SessionArray0latency = 00:00:02 k12SessionArray1interaction_id = Interaction_02 k12SessionArray1correct_response = c k12SessionArray1student_response = c k12SessionArray1result = C k12SessionArray1latency = 00:00:02 k12SessionArray2interaction_id = Interaction_03 k12SessionArray2correct_response = a k12SessionArray2student_response = a k12SessionArray2result = C k12SessionArray2latency = 00:00:03 now here's the format of the database AnswerID | UserID | InteractionID | CorrectResponse | StudentResponse | QResult | Latency How do I insert the data to have it be mulitiple rows like: 1 | 46 | Interaction_01 | d | d | C | 00:00:02 2 | 46 | Interaction_02 | c | c | C | 00:00:02 3 | 46 | Interaction_03 | a | a | C | 00:00:03

View 4 Replies View Related

Displaying Data From Multiple Rows On 1 Row

Aug 22, 2005

I would like to display data from one column on multiple rows in one row.

Example:

Name Pet
David Dog
Dawn Dog
Dawn Cat
Pete Mouse
Pete Cat
Pete Dog

I would like the result to do the following:

David Dog
Dawn Dog Cat
Pete Dog Cat Mouse

I will not know the row values ahead of time.

View 2 Replies View Related

Breaking A Row Of Data Into Multiple CSV Rows

Jun 19, 2007

Does anyone have a routine that takes a row of data from database, duplicates/triplicates it, appends some information to it and writes it out as 2/3 CSV rows.

Basically I need to do the following.

Selected from database:
Row1 Col1 Col2 Col3

Output:
Row1 Col1, Col2, Col3, abc
Row1 Col1, Col2, Col3, def
Row1 Col1, Col2, Col3, ghi

Thank you

View 5 Replies View Related

Combine Data From Multiple Rows

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

Selecting Data From Multiple Rows

Jan 22, 2008

Here's my tables:

Students
-----------------------
StudentID | StudentName
-----------------------

StudentClasses
-----------------------
StudentID | ClassName
-----------------------

I'm trying to put data into a gridview in ASP.NET 2.0 like this:

Student | Classes
-----------------
Name1 | ClassName1 ClassName2 ClassName3
Name2 | ClassName1
Name3 | ClassName1 ClassName2

I just need to retrieve names of each class that a student is taking, then print them out one after another.

View 1 Replies View Related

Select Data From Multiple Rows To One Row

Feb 27, 2008

Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.

stocknumber transtype transsubtype balance

s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100

s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300

I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below

stocknumber in,deal out,cust in,cust
s01 300 300 100
s02 200 500 400

I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .

View 7 Replies View Related

Getting Data From Multiple Rows Into One Column

Dec 3, 2005

I have a table that has values as follows:PersonID Degree55 MD55 Phd55 RN60 MD60 PhdI need a create a query that will give me output like this:PersonID Degree55 MD, Phd, RN60 MD, PhdAny ideas

View 16 Replies View Related

Updating Data In Multiple Rows..

Sep 20, 2007

Hi all..

First of all Thanks for all the help, I received over the years from MSDN..

Here is my new problem...

I have a SQL table like following table..

State

City

StartDt

EndDt

1

AK

ANCHORAGE

4/1/2007

12/31/2049

2

AK

ANCHORAGE

4/1/2007

12/31/2049

3

AK

ANCHORAGE

5/1/2006

3/31/2007

4

AK

ANCHORAGE

5/1/2006

3/31/2007

5

AK

ANCHORAGE

6/1/2004

4/30/2006

6

AK

ANCHORAGE

6/1/2004

4/30/2006

7

AK

COLDFOOT

10/1/2006

12/31/2049

8

AK

COLDFOOT

10/1/1999

12/31/2049

Now here is what I want to do��
1> Sort the table based on Start Date (the picture shown is already sorted..) for example first 6 rows for AK - Anchorage
2> select the rows with same city (rows 1-6)
3> Select the rows with distinct start date (rows 1-3-5)
4> Change the End Date of the second selected row (row 3 in this case) to I day below the start date of 1 selected row. (4/1/2007 �� 1 day = 3/31/2007)
5> proceed till end of selected rows.. 1-3-5
6> do the same thing for rows 2 and 4.
7> follow the same procedure for rest of the file.

The selected file should look like this when done..

State

City

StartDt

EndDt

1

AK

ANCHORAGE

4/1/2007

12/31/2049

2

AK

ANCHORAGE

4/1/2007

12/31/2049

3

AK

ANCHORAGE

5/1/2006

3/31/2007

4

AK

ANCHORAGE

5/1/2006

3/31/2007

5

AK

ANCHORAGE

6/1/2004

4/30/2006

6

AK

ANCHORAGE

6/1/2004

4/30/2006

7

AK

COLDFOOT

10/1/2006

12/31/2049

8

AK

COLDFOOT

10/1/1999

9/30/2006

Is there way to do this in SSIS? any recommened appprach>?

Any help with this is highly appreciated..
Thank You..

View 1 Replies View Related

Retrieving Multiple Rows From Data Base.

Sep 15, 2006

OK here's my question. I want to retrieve from my database employee table all those employees with the name eg. Smith and display them in a list. Can anyone give me any pointers please. I'm using VB 2005 Express Edition. So far this is what I have but it only seems to return 1 row when I know there are more than one entries with the name I am inputting

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim searchString As String

searchString = Me.SearchStringBox.Text

Try

Dim filter As String

filter = "LastName LIKE '" & searchString & "'"

Dim search() As System.Data.DataRow

search = myCDDataSEt.ClientData.Select(filter)

If search.Length > 0 Then

'no code as yet

Else

MessageBox.Show("The client " & searchString & "is not in the database")

End If

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

View 2 Replies View Related

SQL 2012 :: Split Column Data Into Multiple Rows

Apr 1, 2015

How to split a column data into multiple rows, below is the requirement...

Create table #t3 (id int, country (varchar(max))

INSERT #t3 SELECT 1,' AU-Australia
MM-Myanmar
NZ-New Zealand
PG-Papua New Guinea
PH-Philippines'

Output should be like below

1 ,AU-Australia
1,MM-Myanmar
1,NZ-New Zealand
1,PG-Paua New Guinea
1,PH-Phlippines

Note: we are getting source data from sqlserver tables.

I googled and found below way but did't get the output as required

SELECT A.id, a.country,
Split.a.value('.', 'VARCHAR(500)') AS String
FROM (SELECT id, country ,
CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String
FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);

View 4 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

Select Multiple Rows Of Data Not From Any Pre-existing Table

Apr 29, 2008

I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?

View 3 Replies View Related

SQL Server 2014 :: How To Pivot Output Data Into Multiple Rows

Nov 4, 2015

Is it possible to pivot the output data into multiple rows?

I wanted one row for deleted data and another row for Inserted data, I was looking at UNION ALL and CROSS APPLY but to no avail.

View 9 Replies View Related

Loading Data From Multiple Rows Into Single Row In Excel Sheet

Jan 9, 2008

Hi,

I want to load data into Excel file with following format,

Country

State

Total

Location

ABC

A

20

X1

30

Y1

C

100

XYZ

X

40

Basically I want to insert records from multiple rows into a single row; how can I achieve this using SSIS.
I am using Excel as a data source.

Any help is appreciated.

Regards,
Omkar.

View 8 Replies View Related

Data Script Task - How To Generate Multiple Rows From One Row ? (string Splitter)

Nov 28, 2006

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

Thibaut

View 9 Replies View Related

Merge Multiple Rows Into A One Or More Rows With Multiple Columns

May 7, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

ID Pat_ID

1 A

2 A
3 A
4 A
5 A
6 B

7 B
8 B
9 C

10 D

11 D

I want output for the above as:

Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null

Please help me. Thanks!

View 6 Replies View Related

Transact SQL :: Create Email Report Which Gives Result Of Multiple Results From Multiple Databases In Table Format

Jul 24, 2015

I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:

EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',

[code]....

View 3 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

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

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!

View 1 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

Reporting Services :: How To Create Report With Multiple Rows With One Parent And Multiple Child Groups

Aug 17, 2015

I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.

View 3 Replies View Related

SQL Server 2012 :: Concatenate Multiple Rows In Multiple Columns

Aug 5, 2014

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?

View 1 Replies View Related

SQL Server 2012 :: Multiple Rows Into Multiple Columns?

Mar 2, 2015

I have the following results:

ID, Office1
1, Testing
1, Hello World

What i am trying to do is to get this result:

ID, Office1, Office2
1, Testing, Hello World

how i can accomplish this task.

View 3 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Parent Table - (multiple) Rows Into (multiple) Columns

Feb 12, 2015

I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:

I need results for each parent like this

ParentID, LastName, FirstName, [Kid1Name,Kid2Name,Kid3Name], [Kid1Age,Kid2Age,Kid3Age],[kid1grade,Kid2grade,Kid3grade],[kid1gender,Kid2gender,Kid3gender]

View 1 Replies View Related

Transact SQL :: To Show Multiple Column In Multiple Rows

Aug 14, 2015

I have the following database structure

Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4

How can I show Each Depth column as seperate row

AAA 1
AAA 2
AAA 1 as follows

View 3 Replies View Related