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?
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
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 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.
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:
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 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 previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
I am trying to figure out if there is a way to convert (unlimited numberof) rows into columns in SQL Server? Can this be done using a SQL query and without creating any extra tables or procedures?
Hi, I want to convert multiple rows to one row and multiple columns. I saw some examples with PIVOT but i could not get them to work. Heres what i want to do:
This is the how the table is: EmpID Designation
678 CFA
679 CFA
680 CFA
685 CFP
685 CIMA
685 IMCA
I want it to display as: EmpID Designation1 Designation2 Designation3 678 CFA 679 CFA 680 CFA 685 CFP CIMA IMCA
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns FROM table WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1') AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts ( id INT PRIMARY KEY, phone1 VARCHAR(15), phone2 VARCHAR(15) )
-- Insert the records for type 1
INSERT INTO #Contacts SELECT id, phone_num, NULL FROM test1 WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts SELECT id, NULL, phone_num FROM test1 WHERE NOT EXISTS ( SELECT 1 FROM #Contacts WHERE #Contacts.id = test1.id ) AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts SET phone2 = test1.phone_num FROM #contacts JOIN test1 ON test1.id = #Contacts.id WHERE type_code = '2' SELECT id, phone1, phone2 FROM #Contacts DROP TABLE #Contacts
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
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.
I am getting error when I passed multiple rows in less than condition:
create table #t1 ( ID int) INSERT INTO #t1
SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8 CREATE TABLE #t2 (ID int) INSERT INTO #t2 SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4
SELECT ID FROM #t2 WHERE ID < (SELECT ID FROM #t1)
Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX); SET @columns = N''; SELECT @columns += N', p.' + QUOTENAME(deck) FROM (SELECT p.deck FROM dbo.report AS p GROUP BY p.deck) AS x;
[Code] ....
I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX] ON [dbo].[_IMPORT_FILES_CONTACTS] AFTER INSERT AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT [Employee ID] = emp.employee_id, [Coverage Code 1] = enr.coverage_code, [Coverage Date 1] = enr.coverage_date, [Coverage Code 2] = case when enr.product_type = 'Accident.Accident' then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
