Hello,
I have a delima, and im not really sure if this possible. But i have a table like lets say
id | data1
1 this
2 that
3 stuff
i want to be able to return this as one row with the data from data1 in one column seperated by commas.
so the result would be
1 Column
this, that, stuff
can anyone help me with this.
Thank you,
~ Moe
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
IS there a way to combine all matching rows in a table so that itoutputs as one row, for example:tblMyStuffUniqueID int IDENTITYParentID intSomeSuch nvarchar(50)SomeSuch2 nvarchar(50)Table data:UniqueID ParentID SomeSuch SomeSuch21 1 Dog Bark2 1 Cat Meow3 3 Cow Moo4 3 Horse Whinnie5 5 Pig OinkDesired query result from Query:SELECT ??? as myText from tblMyStuff WHERE ParentID = 3myText = Cow Moo, Horse WhinnieHelp is appreciated,lq
View 2 Replies View RelatedWith 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
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
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 RelatedHi 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 RelatedI have the table below and like to combine the rows to create a single link row in a new column. The rows should be combined based on the job number columns which is the same for the rows to be combined.
DECLARE @M31
( M31_SQL_ID INT
,JOB_NUMBER INT
,LINE_NUMBER INT
,WORKS_DESC VARCHAR)
[Code] ...
Output should be as below
219242
16/7/15 called tenant and she thought we would just fix for free - advised her I can get a quote how ever she may have to pay - she will call back
219245
16/7/15 called tnt said no report number. Said she will speak with her husband and call back with her decision and 16/07/15 the work order was sent to agent ...
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
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..
hai,
i have the data like this
Id [name] value
------------------------------
1 sam abc
1 sam def
1 sam ghi
2 john abc
2 john def
for a unique Id all the fields are same except the value.Now I want to join them and the data should appear like below.
Id [name] value
------------------------------------
1 sam abc,def,ghi
2 john abc,def
please help me regarding the query.
thanks
from,
pavansagar
Hi!I have a table looking like(username) (account number) (start date) (end date) (product)wich I can have up to 4 lines for the same client.I wist to transfert those lines into a new table looking like(username) (account number) (start date 1) (end date 1) (product 1)(start date 2) (end date 2) ... (product 4)How (in SQL) I could do it?
View 1 Replies View Related
Dear friends,
I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B
TABLE A
SampleCode
Test Name
Result
ID
Name
Sex
9300105
Peripheral Blood Film
....
a few poikilocytes are present.
S7585512E
DHANDAPANI MAHESH
M
9300105
Peripheral Blood Film
....
No blast cells seen.
S7585512E
DHANDAPANI MAHESH
M
9300105
Peripheral Blood Film
....
microcytes, elongated cells and
S7585512E
DHANDAPANI MAHESH
M
9300105
Peripheral Blood Film
....
hypochromic but normocytic: . Some
S7585512E
DHANDAPANI MAHESH
M
9300105
Peripheral Blood Film
....
Majority of rbcs appear slightly
S7585512E
DHANDAPANI MAHESH
M
Output:
TABLE B
SampleCode
Test Name
Result
ID
Name
Sex
9300105
Peripheral Blood Film
....
a few poikilocytes are present, No blast cells seen.microcytes, elongated cells and hypochromic but normocytic. Some Majority of rbcs appear slightly
S7585512E
DHANDAPANI MAHESH
M
Your help would be greatly appreciated
Thanks a lot,
Chicky
Chicky
I am writing a database system which recieves information parsed from various data formats. These data formats may or may not be complete, and as such some rows in the database can have gaps.
The input formats may contain reference to the same row (in this case the same Company record) but hold different facts about that company.
Eg one message may have name, phone and fax, whereas another message may contain name, address and website.
I need to be able to insert new companies into the database OR update current records with extended data as relevant, ive been looking for UPSERT or MERGE queries in SQL Server but i cant find any useful resources explaining its use.
Alternativly i'd like to be able to condense potential duplicates into single rows, eg:
NamePhoneEmailnullNamePhonenullWebsite
which would combine into one row where the null values get set by values derived from other similar rows:
Name PhoneEmailWebsite
Any help at all would be really appreciated
Thanks,
Toby
In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)
Each paragraph of text (SectionText) is in its own row
(SectionNo) Each primary document has a DocumentTypeID of 1 with
three subdocument types (2=Index, 3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo: SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo
For each row that contains the search term, I would like to
return the full document (all rows as parapraphs within one row of
returned data). In other words, I want to reconstitute the full
document as it existed prior to being inserted into the database with
paragraph separation. For exampe, if the search term is in row 3
of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of
that document in one block of text that retains paragraph format
(preferablly with a line break and carriage return between
paragraphs). How can this be done?
How can I get the results of this query>>
SELECT
RoleName = pr.name
,RoleType = pr.type_desc
,PermissionType = pe.state_desc
,Permission = pe.permission_name
,ObjectName = s.name + '.' + o.name
[Code] .....
WHICH RESULTS IN THIS>>
RoleNameRoleTypePermissionType PermissionObjectNameObjectType
publicDATABASE_ROLEGRANTDELETEdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTINSERTdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTSELECTdbo.AgencyUSER_TABLE
[Code] ....
I have one table list this
declare @test table(Id int, Description varchar(500))
insert into @test
values (1, '<b>Name :</b> XUZ <br/><br/>'),
(1, '<b>Type : </b> QWE <br /><br/>'),
(2, '<b>Name : </b> ABC <br /><br/>'),
(2, '<b>Type : </b> FGH <br /><br/>')
My expected result is
1, '<b>Name :</b> XUZ <br/><br/> <b>Type : </b> QWE <br /><br/>'
2, '<b>Name : </b> ABC <br /><br/> <b>Type : </b> FGH <br /><br/>'
As you can see its combine two rows of data group by id. Issue is If I use for xml path('') then its replace my html tags <b> to like <b> so after that then I again i need to replace all these symbols. Is it possible to get expected result without using xml path or if we use xml path then still my html tag leave as it is.
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid. currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
View 12 Replies View RelatedI have an old app that I'm trying to recode and improve performance.
From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.
Here is a quick example of the script
Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE
Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE
Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE
INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'
These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-Scott
I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.
/* will remove for aspx page use */
USE Database
/* these params are on the page in drop down boxes*/
DECLARE @ProductID int;
DECLARE @BuildID int;
DECLARE @StatusID int;
/* static params for this sample */
SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3
/*
the query that will build the datagrid. currently this runs and produces three different result sets.
How do I combine these statements so they produce a single set of results?
*/
IF (@ProductID = -1) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))
IF (@BuildID = -2) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))
IF (@StatusID = -3) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))
Hi,
I have the following query :
select uname, count(ID) from tbh_Axis
group by uname
which works fine and displays
Admin3
User18
How can i display the result as :
Admin(3)
User1(8)
When I do this:
select uname + '(' + count(ID) + ')' from tbh_Axis
group by uname
It doesnt work.
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
Hello,
I have a table which has the following structure:
ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time
How to build a query or store procedure to return result like this:
ID MessageText
001 Hello There Working
003 See you Next Time
Your help/advice is greatly appreciated.
Thanks, Ficisa
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 RelatedTable users:
userid, name, added...
Table groups
groupid, groupname...
Table groupadmins:
userid, groupid
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
Hello,
I need to generate a report, which should display 4 reports. Two tables and some charts. I have all these reports (I mean the .RDL files) individually. I can render the reports separately. But, now the need is to combine these reports in the one RDL file. Is this possible? If yes, how?
Also, I tried to create a stored procedure, which would call all these 4 SP inturn and provide 4 result sets. I thought of have an RDL by calling only this SP which would give 4 result sets. But infortunately, it gave only the first SP's result set. So, I have to combine the 4 RDL files into one to show on the Reporting Console. Can anyone please help me in this? Help would be grately appreciated.
Thanks a lot. Let me know if the question is not clear.
Mannu.
Table:
CREATE TABLE [dbo].[KPI](
[SVP] [varchar](20) NULL,
[Wk1] [int] NULL,
[Wk2] [int] NULL,
[Wk3] [int] NULL,
[Wk4] [int] NULL,
[Wk5] [int] NULL,
[Y] [int] NULL,
[Q] [int] NULL,
[Wk] [int] NULL
)
To generate sample data:
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)
[Code] ....
Current result:
SVPWk1Wk2Wk3Wk4Wk5YQWk
SVP10000201411
SVP02000201412
SVP00300201413
SVP00040201414
[Code] ....
Expected result:
SVPWk1Wk2Wk3Wk4Wk5YQ
SVP1234020141
SVP30 1 2 6 9 20142
SVP103226820143
SVP17233141120144
I surely can loop each row and insert the needed value into the result, I want to know if there is a better way to generate the result.
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!!!!!
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?
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!
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.
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.