CrazyEngineers
CrazyEngineers

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

    ankur8819

    ankur8819

    @ankur8819-Y8pKwX
    Updated: Oct 25, 2024
    Views: 1.0K
    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]
    0
    Replies
Howdy guest!
Dear guest, you must be logged-in to participate on CrazyEngineers. We would love to have you as a member of our community. Consider creating an account or login.
Replies

  • ankur8819

    Member • Feb 9, 2012

    Pasting the Screen shot for more clarification Untitled
    Are you sure? This action cannot be undone.
    Cancel

  • PraveenKumar Purushothaman

    Member • Feb 9, 2012

    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!!! 😁
    Are you sure? This action cannot be undone.
    Cancel

  • PraveenKumar Purushothaman

    Member • Feb 9, 2012

    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! 😀
    Are you sure? This action cannot be undone.
    Cancel

  • ankur8819

    Member • Feb 9, 2012

    Thanks Praveen
    I did it using the below query.
    Select a,stragg(b),c from ankur group by a,c
    Are you sure? This action cannot be undone.
    Cancel

  • PraveenKumar Purushothaman

    Member • Feb 9, 2012

    ankur8819
    Thanks Praveen
    I did it using the below query.
    Select a,stragg(b),c from ankur group by a,c
    Awesome! 😀
    Are you sure? This action cannot be undone.
    Cancel

  • RAANA

    Member • Mar 5, 2012

    can anybody plz help me to write the query for copying data from one table into another in C#?
    Are you sure? This action cannot be undone.
    Cancel

  • PraveenKumar Purushothaman

    Member • Mar 5, 2012

    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: #-Link-Snipped-#
    Are you sure? This action cannot be undone.
    Cancel

  • PraveenKumar Purushothaman

    Member • Mar 5, 2012

    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 <destination table> from <source table>
 
 
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 <destination table>
from <source table>
 
Example:
Select empId, empFirstName, empLastName, emgAge into employee_backup
from employee
    Use this to copy only the structure of the source table.
    select * into <destination table> from <source table> 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 <destination database.dbo.destination table>
from <source database.dbo.source table>
 
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 <a href="https://vyaskn.tripod.com/code.htm#tagit" target="_blank" rel="nofollow noopener noreferrer">My code library (SQL Server T-SQL code samples, snippets, examples, tips, tricks, stored procedures, user defined functions, VB programs): Narayana Vyas Kondreddi's home page</a> 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).
    Are you sure? This action cannot be undone.
    Cancel
Home Channels Search Login Register