Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Rolling up multiple rows into a single row and column for SQL Server data

MSSQLTips author Douglas P. Castilho By:   |   Read Comments (35)   |   Related Tips: More > T-SQL
Problem

I need a way to roll-up multiple rows into one row and one column.  I know I can roll-up multiple rows into one row using Pivot, but I need all of the data concatenated into a single column in a single row. In this tip we look at a simple approach to accomplish this.

Solution

To illustrate what is needed, here is a sample of data in a table:

Users per sector sales

This is one example of what we want the end result to look like:

sample output

The solution proposed in this tip explores two SQL Server commands that can help us achieve the expected results.  The commands to be used are STUFF and FOR XML.

Preparing Sample Data

Before we begin, we'll create some tables and sample data which the following script will do for us.

CREATE TABLE SALES_SECTORS(
SEC_ID INT,
SEC_NAME VARCHAR(30))
GO
CREATE TABLE USRS(
USR_ID INT,
USR_NAME VARCHAR(30),
SEC_ID INT
)
GO
CREATE TABLE ADV_CAMPAIGN(
ADV_ID INT,
ADV_NAME VARCHAR(30)
)
GO
CREATE TABLE USR_ADV_CAMPAIGN(
USR_ID INT,
ADV_ID INT
)
GO
CREATE TABLE SEC_ADV_CAMPAIGN(
SEC_ID INT,
ADV_ID INT
)
GO
INSERT INTO SALES_SECTORS( SEC_ID, SEC_NAME ) VALUES ( 1, 'ENTERTAINMENT' )
INSERT INTO SALES_SECTORS( SEC_ID, SEC_NAME ) VALUES ( 2, 'CLOTHES' )
GO
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 1, 'ANDERSON', 1 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 2, 'CHARLES', 1 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 3, 'DANNY', 1 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 4, 'LUCAS', 1 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 5, 'KEITH', 2 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 6, 'STEFAN', 2 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 7, 'EDUARD', 2 )
INSERT INTO USRS( USR_ID, USR_NAME, SEC_ID ) VALUES ( 8, 'BRAD', 2 )
GO
INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 1, 'SONY ENTERTAINMENT' )
INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 2, 'BEATS SOUNDS' )
INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 3, 'BOOSE' )
INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 4, 'POLO RALPH LAUREN' )
INSERT INTO ADV_CAMPAIGN( ADV_ID, ADV_NAME ) VALUES ( 5, 'LACOSTE' )
GO
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 1, 1 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 1, 2 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 2, 2 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 2, 3 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 3, 3 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 4, 2 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 5, 4 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 6, 5 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 7, 4 )
INSERT INTO USR_ADV_CAMPAIGN( USR_ID, ADV_ID ) VALUES ( 8, 5 )
GO
INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 1 )
INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 2 )
INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 1, 3 )
INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 2, 4 )
INSERT INTO SEC_ADV_CAMPAIGN( SEC_ID, ADV_ID ) VALUES ( 2, 5 )
GO

STUFF() Function

Before going to the examples, we need to understand the workings of the commands mentioned above. The STUFF() function puts a string in another string, from an initial position.  With this we can insert, replace or remove one or more characters.

This syntax is STUFF(character_expression, start, length, replaceWith_expression):

  • character_expression: string to be manipulated
  • start: initial position to start
  • length: number of characters to be manipulated
  • replaceWith_expression: characters to be used

Here is an example of the how to use the STUFF command. 

For our example we have a string that looks like this:

;KEITH;STEFAN;EDUARD;BRAD

We want to remove the first ; from the list so we end up with this output:

KEITH;STEFAN;EDUARD;BRAD

To do this we can use the STUFF command as follows to replace the first ; in the string with an empty string.

SELECT STUFF(';KEITH;STEFAN;EDUARD;BRAD', 1, 1, '')

And this returns this output:

KEITH;STEFAN;EDUARD;BRAD

FOR XML Clause

The FOR XML clause, will return the results of a SQL query as XML. The FOR XML has four modes which are RAW, AUTO, EXPLICIT or PATH.  We will use the PATH option, which generates single elements for each row returned.

If we use a regular query such as the following it will return the result set shown below.

SELECT 
   SS.SEC_NAME, 
   US.USR_NAME
FROM SALES_SECTORS SS
INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID
ORDER BY 1, 2

Users per sector sales

If we take this a step further, we can use the FOR XML PATH option to return the results as an XML string which will put all of the data into one row and one column.

SELECT
   SS.SEC_NAME,
   US.USR_NAME
FROM SALES_SECTORS SS
INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID
ORDER BY 1, 2
FOR XML PATH('')

Putting It All Together

Example 1

Now that we see what each of these commands does we can put these together to get our final result.

The example query below uses a subquery where we are returning XML data for the USR_NAME from table USRS and joining this to the outer query by SEC_ID from table SALES_SECTORS.  For each value from the inner query we are concatenating a ";" and then the actual value to have all of the data from all rows concatenated into one column.  We are grouping by SEC_NAME to show all USERS within that SECTOR. 

SELECT 
   SS.SEC_NAME, 
   (SELECT '; ' + US.USR_NAME 
    FROM USRS US
    WHERE US.SEC_ID = SS.SEC_ID
    FOR XML PATH('')) [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY SS.SEC_ID, SS.SEC_NAME
ORDER BY 1

The below is the output for this query.  We can see that we have the leading ; in the SECTORS/USERS column which we don't want.

In this modified example, we are now using the STUFF function to remove the leading ; in the string.

SELECT 
   SS.SEC_NAME,
   STUFF((SELECT '; ' + US.USR_NAME 
          FROM USRS US
          WHERE US.SEC_ID = SS.SEC_ID
          FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY SS.SEC_ID, SS.SEC_NAME
ORDER BY 1

And we get this resultset:

If we also want to order the SECTORS/USERS data we can modify the query as follows:

SELECT 
   SS.SEC_NAME,
   STUFF((SELECT '; ' + US.USR_NAME 
          FROM USRS US
          WHERE US.SEC_ID = SS.SEC_ID
          ORDER BY USR_NAME
          FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY SS.SEC_ID, SS.SEC_NAME
ORDER BY 1

Example 2

If we want this all to be in one column we can change the query a little as follows:

SELECT 
   SS.SEC_NAME + ': ' +
   STUFF((SELECT '; ' + US.USR_NAME 
          FROM USRS US
          WHERE US.SEC_ID = SS.SEC_ID
          FOR XML PATH('')), 1, 1, '') [SECTORS/USERS]
FROM SALES_SECTORS SS
GROUP BY SS.SEC_ID, SS.SEC_NAME
ORDER BY 1

And this gives us this result:

Users concatenated by sale sectors

Example 3

This example takes it a step further where we have multiple subqueries to give us data based on USERS within CAMPAIGNS within SECTORS.

SELECT 
    SS.SEC_ID, 
    SS.SEC_NAME,
    STUFF((SELECT '; ' + AC.ADV_NAME + ' (' + 
             STUFF((SELECT ',' + US.USR_NAME
                    FROM USR_ADV_CAMPAIGN UAC
                    INNER JOIN USRS US 
                          ON US.USR_ID = UAC.USR_ID
                    WHERE UAC.ADV_ID = SAC.ADV_ID
                    FOR XML PATH('')), 1, 1, '') + ')'
           FROM ADV_CAMPAIGN AC
           INNER JOIN SEC_ADV_CAMPAIGN SAC 
                 ON SAC.ADV_ID = AC.ADV_ID AND SAC.SEC_ID = SS.SEC_ID 
           ORDER BY AC.ADV_NAME
           FOR XML PATH('')), 1, 1, '') [CAMPAIGNS/USERS PER SECTOR]
FROM SALES_SECTORS SS
GROUP BY 
     SS.SEC_ID, 
     SS.SEC_NAME

Users/Campaigns concatenated by sale sectors

Conclusion

There are always several options to complete a task within SQL Server and we should take the time to explore the capabilities offered by the database before developing large and complex code. I hope this is one more of those examples that shows there are sometimes easier approaches than you think might be available.

Next Steps
  • Take this further and create simple queries and then deepen the complexity of the code.
  • Explore the commands used in this tip further to see what other things you might be able to do.
  • Some more details about the commands used above can be obtained from MSDN using the links below:


Last Update: 4/5/2013


About the author
MSSQLTips author Douglas P. Castilho
Douglas Castilho has been a SQL Server DBA over 6 years, focuses on tuning, backup, disaster recovery, mirroring, T-SQL, PL-SQL and .NET

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, April 05, 2013 - 1:19:55 PM - Joe Celko Read The Tip

Ever hear of First Normal Form (1NF)  and tiered architectures? You are violating both. This is done in a presentation layer in a correctly designed system. 


Friday, April 05, 2013 - 3:09:26 PM - Greg Robidoux Read The Tip

@Joe - thanks for the feedback and I agree this is more of a presentation aspect than SQL, but sometimes SSMS might be the one and only presentation layer there is.  I had the a recent need to roll-up information the same way and there was no front-end layer just T-SQL queries.


Friday, April 05, 2013 - 5:23:52 PM - LadyRuna Read The Tip

You posted this just in the nick of time. A moment ago, I had a coworker come up to me and ask how can he create t-SQL code to roll up multiple rows into a single row and column and all I had to do was send him the link to this tip.  :) 


Saturday, April 06, 2013 - 2:57:32 PM - Douglas Read The Tip

Thank you Joe for the feedback and Greg for your reply. I used only simple T-SQL queries in examples.


Saturday, April 06, 2013 - 3:04:40 PM - Douglas Read The Tip

Hi LadyRuna.

Cool, I'm glad it helped. I'll explore more alternatives for different needs that we always have.


Saturday, April 06, 2013 - 7:10:58 PM - JustpassingBy Read The Tip

Or 

you could just make a scalar valued function  AND call that as a Column

CREATE FUNCTION dbo.[getSectorUsersBySectorName]( @CurrentSectorName VARCHAR(30) )
RETURNS VARCHAR(MAX)
AS
BEGIN

       DECLARE @SectorUsers VARCHAR(MAX) = ''  

       
        SELECT @SectorUsers = @SectorUsers 

 + ISNULL(US.USR_NAME + '; ' , '')  

FROM SALES_SECTORS SS
INNER JOIN USRS US ON US.SEC_ID = SS.SEC_ID
         WHERE SS.SEC_NAME = @CurrentSectorName

-- @SectorUsers  wil hold A concetinated line with al users
        -- Return the result of the function

RETURN @SectorUsers
END

AND use it like so

SELECT distinct

   SS.SEC_NAME, 

   dbo.getSectorUsersBySectorName(SS.SEC_NAME) [SECTORS/USERS]

FROM SALES_SECTORS SS

ORDER BY 1 



 


Monday, April 15, 2013 - 12:44:40 PM - thomasD Read The Tip

I could have used this about 3 months ago. :-) Instead I ended up with some complex TSQL. Thanks for the info, I'll certainly be using this in the future!


Tuesday, April 30, 2013 - 5:21:50 PM - leon jhirad Read The Tip

hi,

this is what looking for, i'ts a great, just quation how do i replace ";" with new line like chr(13).

 

can i do it ?

 

thanks

jhirad leon


Monday, May 06, 2013 - 1:39:37 PM - sunil kumar jain Read The Tip

this is very nice example for manupulate muliple data (rows and coulmn) in a single.

 


Saturday, May 25, 2013 - 8:56:59 AM - Javed Read The Tip

 

 

Hi,

I agree with this example 

  thank's for clarify my doubt


Sunday, May 26, 2013 - 2:47:56 PM - David Read The Tip

I hate it when people poo poo on things because it's not to their standard.

If someone came to you and asked for a one time extract of data in this format, are you going to turn them down and tell them they need to have a complete solution developed with all the different tiers to get the information they need?  No.

Yes, it may not be ideal, but not all of us work in an ideal world.  Sometimes people need data in a specific format now vs. later.  In a few minutes you could turn around and hand them the information they need vs. spending an hour explaining why you can't do something because it violates some standards that they're not familiar with or probably don't even care.


Thursday, May 30, 2013 - 10:04:52 AM - Nikheel Read The Tip

Hi  Douglas,

This artical is one of the most important and healf full for every one.

It solved my problem in very effiecnt and optimised way.

great article.

Thanks,


Thursday, July 04, 2013 - 10:22:56 AM - Banwari Read The Tip

 

Thanks for sharing the great one.

I am trying to show only 3 value and for rest , I want to sum the number of values. For example

in below figure Entertainment contains values(Anderson. charles,Danny, lucas.) . So for this I want (Anderson,Charles and 2 other)

 Facebook notification is the best live example when you see any link it shows you Anderson,Joy and 20 other commented on  your post. Hope you get me

sample output

How can I get this result? 

 

Thursday, July 04, 2013 - 11:07:17 AM - Douglas P. Castilho Read The Tip

Hi Banwari,

I think this example will help you in your need

 

SELECT

   SS.SEC_NAME,

   STUFF((SELECT TOP 2 '; ' + US.USR_NAME 

          FROM USRS US

          WHERE US.SEC_ID = SS.SEC_ID

          FOR XML PATH('')), 1, 1, '') + ' AND ' + (SELECT CAST(COUNT(U.USR_NAME)-2 AS VARCHAR) + ' other'

                                                      FROM USRS U

                                                     WHERE U.SEC_ID = SS.SEC_ID) [SECTORS/USERS]

FROM SALES_SECTORS SS

GROUP BY SS.SEC_ID, SS.SEC_NAME

ORDER BY 1

 
I used "TOP 2" and this "2" value was subtracted in the "SELECT COUNT(...)" clause.
Try this example ok.
 
Have a nice day!
 

Friday, July 05, 2013 - 1:21:03 AM - Banwari Read The Tip

 

Thanks for replying , 

It working well if it i have more values to show , but it's not workig for less data. 

3 kumawat.ban@gmail.com; kumawat.ban@gmail.com AND 45 other

4 kumawat.ban@gmail.com AND -1 other

On no:3 I am having 47 times kumawat.ban@gmail.com , so it is displaying well,

but on no :4 I have only one value and it is displaying  kumawat.ban@gmail.com AND -1 other  it meansa there are two values which is not right . It shold display only  kumawat.ban@gmail.com 


Tuesday, July 09, 2013 - 5:48:50 AM - Saurav Read The Tip

How did you get rid of the column names in the SQL result?


Friday, July 12, 2013 - 12:47:06 PM - Brian Nordberg Read The Tip

I've done the ForXML on huge datasets - billions of rows, with massive healthcare documents. It is amazingly fast. However watch out for Unicode values! XML can't handle them.


Tuesday, July 16, 2013 - 6:19:24 AM - priya Read The Tip

 

how will you use delete query for the multivalued column.

Eg. Table "DETAILS" contains the columns such as "NAME", "Email-id"

Name   Email-id

aaa      aaa@gmail.com,abc@gmail.com,ccc@gmail.com

QUERY : How will you delete abc@gmail.com from the column Email-id????

NOTE : other value should not be deleted(aaa@gmail.com,ccc@gmail.com)

please share it as soon as possible

 


Wednesday, July 31, 2013 - 9:59:43 AM - tCubeR Read The Tip


--for the reporting I like the astric delimiter:
--===============================================

SELECT
    SS.SEC_ID,
    SS.SEC_NAME,
    STUFF((SELECT '*' + AC.ADV_NAME + ' (' +
             STUFF((SELECT ',' + US.USR_NAME
                    FROM USR_ADV_CAMPAIGN UAC
                    INNER JOIN USRS US
                          ON US.USR_ID = UAC.USR_ID
                    WHERE UAC.ADV_ID = SAC.ADV_ID
                    FOR XML PATH('')), 1, 1, '') + ')'
           FROM ADV_CAMPAIGN AC
           INNER JOIN SEC_ADV_CAMPAIGN SAC
                 ON SAC.ADV_ID = AC.ADV_ID AND SAC.SEC_ID = SS.SEC_ID
           ORDER BY AC.ADV_NAME
           FOR XML PATH('')), 1, 1, '') [CAMPAIGNS_USERS_PER_SECTOR]
FROM SALES_SECTORS SS
GROUP BY
     SS.SEC_ID,
     SS.SEC_NAME

;


--in your SSRS report add a field with an expression:
--=======================================================

--set expression for:
/*
=IIf(Len(Fields!CAMPAIGNS_USERS_PER_SECTOR.Value) > 0, Replace(Fields!CAMPAIGNS_USERS_PER_SECTOR, "*", vbCrLf), "")


Results:
--===========================
CAMPAIGNS_USERS_PER_SECTOR

BEATS SOUNDS (ANDERSON,CHARLES,LUCAS)
BOOSE (CHARLES,DANNY)
SONY ENTERTAINMENT (ANDERSON)

*/

 


Thursday, August 08, 2013 - 12:38:32 PM - Miles Read The Tip

I am currently in a situation where I need to get a comma delimited file created. I need to evaluate users based on matching SSNs, but not matching member codes. This determines if the base user has a spouse, and/or children. I would then need this to add up to some value, based on matching SSNs, and assign the appropriate number if the SSNs match, but the member codes do not. For example, the base user would have a value of "1", while the spouse would have a value of "2", so for the sake of this process, they would have a total value of "3", and then I would need to assign the appropriate field value to a flat file using SSIS. Adding to this is those with 4 or 5 children, thus having 6 or 7 records to evaluate. This can get even trickier if there are matching SSNs, with not matching member codes, but then also that they do not share the same termination date. This means that one person, either a spouse or dependent, is no longer considered part of that "family group", thus have a different value assigned to them than the rest of the "family".

 

I thought about rewriting the VB code from the original application, but this has become a huge undertaking, since it was setup to create an xml file using an array, so I setup an SSIS package for this instead. I have most of it completed, but how to populate the one field is stumping me. I thought about using a Cursor, but I may have to evaluate up to 6 or 7 rows, depending on how many children are involved, so I am not sure if this is going to work or not.

 

Does anyone have any suggestions on where to start with getting SQL code together that can accomplish this?


Thursday, August 08, 2013 - 12:48:27 PM - alotus Read The Tip

Hi - This is great! Thank you!

However, I'm hoping you can help me with one thing:

I have used your SQL code to do what I need to for the most part with my DB, but if I need to add a count column and sum the results, I'm not sure how to do that. For example, let's say I return the following: 

A11Perth, AustraliaAP-PERTH; AP-PERTHBAN

If I add in a count column at the end of the select query, it displays the following:

A11 Perth, Australia AP-PERTH; AP-PERTHBAN 128 A11 Perth, Australia AP-PERTH; AP-PERTHBAN 282

What I really need is for it to look like the 1st output, but have 128 and 282 summed into 410. This is just one example with two results. I have a few other examples in the same table where there are as many as 10 or 12 rows that need to be summed.

Any thoughts? Thanks!


Tuesday, August 13, 2013 - 11:14:44 AM - Douglas P. Castilho Read The Tip

Hi alotus,

I don´t know if the code below help you, because I don´t have more details about your needs, but I think you can understand what we can do with the code below, I´ll wait your feedback, I hope to have helped you.

 

CREATE TABLE #Tb_A(

COL_1 VARCHAR(100),

COL_2 INT

)

 

INSERT INTO #Tb_A(COL_1, COL_2) VALUES ('A11 Perth, Australia AP-PERTH; AP-PERTHBAN', 128)

INSERT INTO #Tb_A(COL_1, COL_2) VALUES ('A11 Perth, Australia AP-PERTH; AP-PERTHBAN', 282)

 

SELECT 

   STUFF((SELECT DISTINCT A.COL_1 + ' ' + 

          (SELECT CAST(SUM(B.COL_2) AS VARCHAR) FROM #Tb_A B WHERE B.COL_1 = A.COL_1) as "text()"

          FROM #Tb_A A

          FOR XML PATH('')), 1, 1, '') RESULT

 

DROP TABLE #Tb_A

This simple example returned:

11 Perth, Australia AP-PERTH; AP-PERTHBAN 410

 

Thank you.

Have a great day!


Thursday, October 03, 2013 - 2:06:14 PM - saadee Read The Tip

hi sir.i want to convert single column into multiple columns.

here is the table structure

------- create a table having single column
--- col whose data is A,B,C,D,E,F,G,H,I,J
---- WRITE A QUERY TO GET AN OUTPUT AS
----- COL1    | COL2    | COL3
-------    A        B        C
--          D        E        F
--          G        H        I
--          J        NULL    NULL

help out to solve or mail me answer on saadee484@gmail.com


Saturday, October 19, 2013 - 6:13:29 AM - Baiju Read The Tip
  1. in time and out time from query

02-20-2013 12:18 PM|LINK

I have a  table Events

Events containing

cardno,time,id,name--each id has a cardno



my requirement is every day each employee swipe the card several times

i want to calculate first and last time of each card

the output should be

name     1                       2                             6        7  
           in       out            in        out                    holiday   holiday
xxx      09:30     06:30      09:40   06:45
where 1,2...... are dates for example january 1,2, etc. 6 and 7 are saturday and sunday

how it is posssible

Regards
Baiju


Saturday, November 02, 2013 - 11:54:26 AM - talal Read The Tip

Hello

I have a table containing 2 columns describing friendship relations

declare @T table (Name nvarchar(50), Friends nvarchar(50))

insert into @T values 

('Joe','Jean'),

('Mike','Jean'),

('Jean','Zaki'),

('Zaki','Ali'),

('Mimi','Nana'),

('Chico','Yari')

...

My question is how to get the integral list of friend of Joe (Joe,Jean,Mike,Zaki,Ali) using Sql Server

Thanks in advance


Friday, December 13, 2013 - 4:30:46 PM - rodrigo Read The Tip

Great article!

 

 


Thursday, December 19, 2013 - 2:42:11 AM - Alekhya Read The Tip

 

I have one question:

 

My case is :

 

Contract     R/I                   LCEAMTSURP     

Number      Account                              

                  No                                   

03005909  MR03FR                 2,300,000     

03005909  13FRST1                        0     

03005909  13PPXL2                        0     

03005909  13PPXL3                        0     

 

Now i need this format in teh below way in order to remove the duplicate data:

 

Contract Number      MR03FR | 13FRST1 | 13PPXL2 | 13PPXL3

03005909                2,300,000| 0 | 0 | 0

 

How can i do this? Please do help.... 


Thursday, January 23, 2014 - 8:32:13 PM - Douglas P. Castilho Read The Tip

Hi saadee,

What you need is something like this?

 

CREATE TABLE TEST (

COL CHAR(1) )

 

INSERT INTO TEST (COL) VALUES ('A')

INSERT INTO TEST (COL) VALUES ('B')

INSERT INTO TEST (COL) VALUES ('C')

INSERT INTO TEST (COL) VALUES ('D')

INSERT INTO TEST (COL) VALUES ('E')

INSERT INTO TEST (COL) VALUES ('F')

INSERT INTO TEST (COL) VALUES ('g')

INSERT INTO TEST (COL) VALUES ('H')

INSERT INTO TEST (COL) VALUES ('I')

INSERT INTO TEST (COL) VALUES ('J')

 

DECLARE @INDEX INT = 1

DECLARE @COLS VARCHAR(20) = 

(

SELECT 

   DISTINCT

   STUFF((SELECT UPPER(TB2.COL)

          FROM TEST TB2

          FOR XML PATH('')), 1, 0, '') COL

FROM TEST TB1

)

 

SELECT SUBSTRING(@COLS, @INDEX, 1) AS COL1,

       SUBSTRING(@COLS, @INDEX + 1, 1) AS COL2,

  SUBSTRING(@COLS, @INDEX + 2, 1) AS COL3

UNION ALL

SELECT SUBSTRING(@COLS, @INDEX + 3, 1) AS COL1,

       SUBSTRING(@COLS, @INDEX + 4, 1) AS COL2,

  SUBSTRING(@COLS, @INDEX + 5, 1) AS COL3

UNION ALL

SELECT SUBSTRING(@COLS, @INDEX + 6, 1) AS COL1,

       SUBSTRING(@COLS, @INDEX + 7, 1) AS COL2,

  SUBSTRING(@COLS, @INDEX + 8, 1) AS COL3

UNION ALL

SELECT SUBSTRING(@COLS, @INDEX + 9, 1) AS COL1,

       SUBSTRING(@COLS, @INDEX + 10, 1) AS COL2,

  SUBSTRING(@COLS, @INDEX + 11, 1) AS COL3

 
Let me know if this simple example helped you ok.
Thanks!!!

Thursday, January 23, 2014 - 9:15:19 PM - Douglas P. Castilho Read The Tip

Hi talal, what you need is this ?

 

declare @T table (Name nvarchar(50), Friends nvarchar(50))

insert into @T values 

('Joe','Jean'),

('Mike','Jean'),

('Jean','Zaki'),

('Zaki','Ali'),

('Mimi','Nana'),

('Chico','Yari')

 

DECLARE @F table (FriendsOfJoe nvarchar(50))

DECLARE @INDEX INT = (SELECT COUNT(*) FROM @T)

WHILE (@INDEX >= 1)

BEGIN

INSERT INTO @F (FriendsOfJoe) SELECT Friends FROM @T WHERE Name = 'Joe' AND NOT EXISTS (SELECT 1 FROM @F WHERE FriendsOfJoe = Friends)

INSERT INTO @F (FriendsOfJoe) SELECT Friends FROM @T WHERE Name IN (SELECT FriendsOfJoe FROM @F) AND NOT EXISTS (SELECT 1 FROM @F WHERE FriendsOfJoe = Friends)

INSERT INTO @F (FriendsOfJoe) SELECT Name FROM @T WHERE Friends IN (SELECT FriendsOfJoe FROM @F) AND NOT EXISTS (SELECT 1 FROM @F WHERE FriendsOfJoe = Name) AND Name <> 'Joe'

SELECT @INDEX -= 1

END

 

SELECT * FROM @F

----------------------
 
Thank you!
 

Monday, March 24, 2014 - 3:23:50 AM - Satish Read The Tip

Thank you Douglas for the nice article !


Tuesday, April 08, 2014 - 6:52:14 AM - Suganya Read The Tip

Great...It was very helpful


Thursday, May 22, 2014 - 9:47:19 AM - Parimal Read The Tip

That was very helpful :) Thanks for that :)

 

Best regards,

Parimal


Tuesday, June 24, 2014 - 3:23:22 AM - sunil Read The Tip

Q) a table consisting of single column like

    sunil@sunmail.com

    lava@sunmail.com

    bhanau@bhanumail.com

 if this above is the situation ,the answer should contain two columns

name     mailid

sunil       sunmail.com

lava        sunmail.com

bhanau   bhanumail.com

what query we have to write to get the above result ..plz help me sir.

thnks in advance


Tuesday, June 24, 2014 - 1:23:09 PM - Douglas Read The Tip

Hi sanil, please follow this example ok:

DECLARE @EMAIL VARCHAR(30)
SET @EMAIL = 'TEST@GMAIL.COM'
SELECT SUBSTRING(@EMAIL, 1, PATINDEX('%@%',@EMAIL)-1) NAME,
       SUBSTRING(@EMAIL, PATINDEX('%@%',@EMAIL)+1, LEN(@EMAIL)-PATINDEX('%@%',@EMAIL)) DOMAIN

You can change the parameter @EMAIL by your table field ok.

Thank you.

Douglas.


Wednesday, July 23, 2014 - 10:50:51 PM - Zaigham Read The Tip

Hi Douglas,

   Thank you so much for creating this excellent article and sharing your knowledge. It was very concise and to the point. Thank you :-)



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.