View Feed
group-icon
Coffee Room
Discuss anything here - everything that you wish to discuss with fellow engineers.
12921 Members
Join this group to post and comment.
ankur8819
ankur8819 • Feb 9, 2012

SQL Query - converting the first data set to the desired format

Hi CEans,
I am stuck with a SQL query .Need few directions.
I have a query that returns rows in the form below
A B C
18184 asas 1
18184 dfdf 1
18185 qwer 2
18185 qwio 2
18186 ank 3
18187 ur 4

I need to change it to below format
A B C
18184 asas,dfdf 1
18185 qwer,qwio 2
18186 ank 3
18187 ur 4
that is for the rows having same values in column 1 and 2 ,the value in column 2 should be comma separated.
I tried using Stragg function in Oracle but could not get it.
Any directions on converting the first data set to the desired format??

Thanks ,
Ankur Luthra
[​IMG]
ankur8819
ankur8819 • Feb 9, 2012
Pasting the Screen shot for more clarification Untitled
Dude, check this out:
select * from ankur;
+-------+------+---+
| A     | B    | C |
+-------+------+---+
| 18184 | asas | 1 |
| 18184 | dfdf | 1 |
| 18185 | qwer | 2 |
| 18185 | qwio | 2 |
| 18186 | ank  | 3 |
| 18187 | ur   | 4 |
+-------+------+---+
 
select A, group_concat(b) as B, C from ankur group by c;
+-------+-----------+---+
| A     | B         | C |
+-------+-----------+---+
| 18184 | asas,dfdf | 1 |
| 18185 | qwer,qwio | 2 |
| 18186 | ank       | 3 |
| 18187 | ur        | 4 |
+-------+-----------+---+
Hope this helps you... πŸ˜€ All the best!!! 😁
I used this query!
CREATE TABLE `ankur` (
  `A` int(11) NOT NULL,
  `B` varchar(255) NOT NULL,
  `C` int(11) NOT NULL,
  PRIMARY KEY (`B`,`C`)
) ENGINE=InnoDB ;
 
 
INSERT INTO `ankur` (`A`, `B`, `C`) VALUES
(
  '18184', 'asas', '1'
), (
  '18184', 'dfdf', '1'
), (
  '18185', 'qwer', '2'
), (
  '18185', 'qwio', '2'
), (
  '18186', 'ank', '3'
), (
  '18187', 'ur', '4'
);
Hope this helps! πŸ˜€
ankur8819
ankur8819 • Feb 10, 2012
Thanks Praveen
I did it using the below query.
Select a,stragg(b),c from ankur group by a,c
ankur8819
Thanks Praveen
I did it using the below query.
Select a,stragg(b),c from ankur group by a,c
Awesome! πŸ˜€
RAANA
RAANA • Mar 5, 2012
can anybody plz help me to write the query for copying data from one table into another in C#?
RAANA
can anybody plz help me to write the query for copying data from one table into another in C#?
Bulk Copy feature of ADO.NET might help you take a look at that: https://msdn.microsoft.com/en-us/library/s4s223c6.aspx
This is the simplest way to copy a table into another (new) table in the same SQL Server database. This way of copying does not copy constraints and indexes.
select * into  from 
 
 
Example:
Select * into employee_backup from employee
We can also select only a few columns into the destination table like below
select col1, col2, col3 into 
from 
 
Example:
Select empId, empFirstName, empLastName, emgAge into employee_backup
from employee
Use this to copy only the structure of the source table.
select * into  from  where 1 = 2
 
Example:
select * into employee_backup from employee where 1=2
Use this to copy a table across two database in the same Sql Server.
select * into 
from 
 
Example:
select * into Mydatabase2.dbo.employee_backup
from mydatabase1.dbo.employee
Any one of the following methods can be employed to copy a table into a destination database on a different SQL Server.
1. Data Transformation Service (DTS) – SQL Server 2000.
2. SQL Server Integration Service (SSIS) – SQL Server 2005
3. SQL Server β€œExport Data” task. – SQL Server 2000/2005
4. Create a linked Server of the destination SQL Server on the source SQL Server and then copy the table. – SQL Server 2000/ 2005.
5. We can also use sp_generate_inserts to generate data insertion scripts and then run the insert scripts.
6. I almost forgot this πŸ˜‰ you can open the source table , select the row(s), copy (ctrl + C) the row(s), open the destination table and then paste (ctrl + V) the row(s).

Share this content on your social channels -