Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:
Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like the query result to look like this:1....First Assignment....05/01/2008....553422....Second Assignment...05/03/2008....33456,523433....Third Assignment....05/07/2008....35225,45121,11553Any suggestions would be appreciated!
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?
I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:
Title - Author - Edition The Amazing Pixies - A. N. Author - Hdbk The Amazing Pixies - A. N. Author - Pbk
Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.
Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri
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
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
) --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
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?
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
I would like to break this into multiple time_in/time_out based on if they have breaks. Breaks are not required and will come across blank if non are taken.
I 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 ...
Date ID Name Job Number JobType 12/12/2007 123456 Fred Smith 111111 Full Day 12/12/2007 654321 Bob Blue 222222 Half Day AM 12/12/2007 654321 Bob Blue 333333 Half Day PM
I need the following output: Date ID Name Job Number JobType 12/12/2007 123456 Fred Smith 111111 Full Day 12/12/2007 654321 Bob Blue 222222 Half Day AM 12/12/2007 654321 Bob Blue 333333 Half Day PM
Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222
Could someone please point me in the right direction?
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
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
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);
Hi i want to create a table as follows :if exists (select * from dbo.sysobjects where id =object_id(N'[Indexes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [Indexes]GOCreate table Indexes(indexname Varchar(100), index_DescriptionVarchar(100), index_keys Varchar(100))GOINSERT INTO Indexes EXEC sp_helpindex 'SDM_Fact_Order_Detail'GOThis will give me a table (northwind)IX_Auto_SDM_Fact_FK_Shipped_Date nonclustered located onSAMIS_SDM_Index FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Dateclustered located onSAMIS_SDM_Data1FK_Insert_Date, FK_Insert_TimeAs you see sp_helpindex will give me a comma seperated field. I wantto split the third column FK_Insert_Date, FK_Insert_Time into a extrarowLike this :IX_Auto_SDM_Fact_FK_Shipped_Date FK_Shipped_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_DateIX_Auto_SDM_Fact_Order_Detail_FK_Insert_Date FK_Insert_TimeCan anyone help me with this?ThanxHennie
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
Campaign and Area are all selectable by parameters, so the actual number of rows is dynamic. What I would like to do is an additional aggregates other then sum for the total - things like average, percent to goal, etc.
And this is where, either I'm completely missing something, or SSRS and I have a huge communication breakdown . I absolutely cannot seem to do this. I tried adding additional columns, but they are grouped under area, not after it - in the above example, it would result in three new columns, one for each area type, not 1. Confusing to describe, but looks something like:
Area1 Area2 Area3 Total Sum Avg Sum Avg Sum Avg CampaignType1 1 xx CampaignType2 2 yy CampaignType3 3 zz Total
I hope that conveys the idea w/o having to fill it all in.
I'm lost as to how to get this accomplished. All I can think of is adding a union dummy row into the actual stored proc to make a different area type (say, AreaAverage) just to add in an additional column and that make sure it sorts at the end. That screams hack to me. Any help????
I have excel files where the column headers I care about are on line 5, and the actual data doesn't begin until line 6. Other than deleting the first 4 lines, which is impractical, how can I get the Excel Connection Manager to import the data correctly? I was able to do this under DTS, so I have to imagine it's possible.
I have a requirement .Where i might have multiple rows one of the row will have description and second row might be related description but wiht routning number null .I want to concatenate these rows
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE FROM @SPLITROW s INNER JOIN master.dbo.spt_values t ON t.type='P' AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK) WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK) WHERE P.PrsnPIISSN_Chr IN ( SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + '''' FROM MassProcessing_Rules PR WHERE PR.intRuleID = 10 )
Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.
