Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (66)   |   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:






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


 



More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, November 11, 2016 - 5:43:17 AM - Brijesh Back To Top

 Please help me with below condition.

I am having SectorName and UserName
SNO ID SECTOR_NAME USERNAME

1      101    Cloths                AAA

2      101    Entertainment     BBB

3      101    Drinks                CCC

4      101    Cloths                DDD

5      222    Cloths                EEE

6      433    Entertainment     FFF 

7      101    Entertainment     GGG

8      222   Drinks                HHH

Required Result:
foR id 101

SNO   CLOTHS          ENTERTAINMENT

 1.         AAA,DDD     BBB,GGG


Wednesday, June 01, 2016 - 10:27:11 AM - Don Goodrich Back To Top

 This example works great for me, it rolls-up the file correctly.

I do however, need to set values for the merged fields (like an array) so the application that will use this file can determine how many appointments need to be sent to the customer.  I've reviewed the articles and none of them can answer this question.

 


Friday, May 27, 2016 - 4:52:16 PM - Trevor Back To Top

Thank you for the clear explanation -- it's helped me twice now.

Now my comment: The code as written removed the leading semicolon, but leaves a leading space in front of the concatenated string.  To fix it, simply adjust the STUFF command telling it to cut off two characters:

STUFF(<text/query>, 1, 2, '')

 


Monday, April 04, 2016 - 10:06:00 PM - Kenneth Tan Back To Top

Thanks a lot Sir!


Monday, March 28, 2016 - 4:08:42 AM - Anon Back To Top

Previously I really struggled to grasp how to implement this concept - finally with your post it makes sense.


Wednesday, March 16, 2016 - 2:53:04 PM - John Spencer Back To Top

 I will say, I never thought I would what is discussed above; however, the above has helped to 'extricate me from several nettles of danger.'  And, thanks for the detailed explanations. 

I will confess to using 'STUFF and the XML portion without really what they did.

 


Sunday, February 28, 2016 - 1:24:19 AM - Christian Albite Back To Top

Hi there. Thanks for the tip. But I have just one issue. I am working on my thesis. Back to my problem, waht if I want to create multiple columns for Users. One column for SEC_NAME, and individual colums for SECTORS/USERS. For example, 1 column cell for Clothes, 1 cell for Keith, other 1 for Stefan, and so on.

SEC_NAME             Sector1        Sector2        Sector3        Sector 4
Clothers                 Keith            Stefan         Eduard         Brad
Entertainment         Anderson     Charles        Danny          Lucas

Well, hoping I could find answers. Thank you. :D 

- Christian

 


Friday, February 19, 2016 - 1:54:43 PM - David Jacobson Back To Top

Doug, thanks for the tip, great stuff (no pun).  I do have a slight issue maybe you could help me with.  My query is not in the right order and I'm not sure how to fix it.  So when my query runs, it gets 5 records like such:

workID     cid     comment
100           2       Test 2
100           3       Test 3
100           5       Test 5
100           4       Test 4
100           1       Test 1

So, I want to get the two most recent comments for record 100 into 1 field.  But now I am getting cid 2,3 and NOT 4,5.  Does that make sense?  When I add the cid to the group by statement I get 6 of the same records.  ARGH!  Can you point me in the right direction?

 David


Wednesday, January 20, 2016 - 8:29:26 AM - laurent Back To Top

 

Thank you for this very explicit, simple and effective tip.

It Solved my problems in a few minutes.

Best regards


Wednesday, December 16, 2015 - 9:20:47 AM - Wojciech Back To Top

Hi,

Great article. It helped me to perform my task I got from my boss today and he is enchanted with what I have done thanks to you!

Thanks a lot.

Wojciech


Friday, November 20, 2015 - 6:29:14 AM - Shailesh Rabari Back To Top

It's Very Nice.

Useful Content.


Thursday, October 15, 2015 - 9:46:43 AM - Matt Back To Top

several years later and this is still relevant.  Thanks!


Wednesday, October 07, 2015 - 5:04:56 AM - Thomas Franz Back To Top

Your syntax has a problem, if the text data contains < or >:

If you

UPDATE ADV_CAMPAIGN SET ADV_NAME = '<SONY> <!--> <ENTERTAINMENT>' WHERE ADV_ID = 1

then your last query will return

 &lt;SONY&gt; &lt;!--&gt; &lt;ENTERTAINMENT&gt; (ANDERSON); BEATS SOUNDS (ANDERSON,CHARLES,LUCAS); BOOSE (CHARLES,DANNY)

If you would replace the FOR-XML-lines by

            FOR XML PATH('i'), root('c'), type
                  ).query('/c/i').value('.', 'varchar(8000)'), 1, 1, '') [CAMPAIGNS/USERS PER SECTOR]
it would return the correct 
 <SONY> <!--> <ENTERTAINMENT> (ANDERSON); BEATS SOUNDS (ANDERSON,CHARLES,LUCAS); BOOSE (CHARLES,DANNY)

instead (feel free to use varchar(max) if necessary).


Friday, September 04, 2015 - 8:41:44 AM - VIJAY Back To Top

Hi ,

 

The data returned for the xmlpath using stuff function returns a lenght of  45678 .

 

But if it is used in the xmlpath it is generating all the rows.

 

select a.col1,+',' from  a 

for xml path('')

This returns all the rows.

 

select stuff((select a.col1,+',' from  a 

for xml path('')),1,1,'') as b

This returns 45678  lenght of data 

 

could you help in resolving this issue

 

 

 


Thursday, August 13, 2015 - 5:30:58 AM - Ying Back To Top

Thank you.  It's exactly what I looking for. :)


Wednesday, July 08, 2015 - 4:29:25 PM - davidh Back To Top

would be nice to have people put very vanilla query using the functions they share. too much going on and someone like myself, newbie, needs to understand basics on it and how to use it plain and simple to take a column with multiple values and place it into one single cell (one column and one row) as the output with comma separation in the next step following.

greatly appreciate anything. thank you.


Friday, June 12, 2015 - 1:03:34 PM - Mena Dawoud Back To Top

Excellent;

but this just in SQL Server Management Studio,

As for Visual Studio it also shows the name of the XML tag <> & </>


Tuesday, June 02, 2015 - 12:35:29 PM - glauco guerrero Back To Top

Thanks  for your solution and taking the time for explaining this tip.

 

 

 


Monday, May 04, 2015 - 8:26:02 PM - Dougal Back To Top

Fantastic explanation, thankyou!


Thursday, March 12, 2015 - 5:04:55 AM - Pacha Back To Top

Thank you!

You guide help me a lots !!


Tuesday, March 03, 2015 - 8:28:47 AM - Squeezer Back To Top

Thanks Douglas for this excelent guide! It's exacly what i was looking for! 

 


Tuesday, January 27, 2015 - 5:40:16 PM - Frankie Back To Top

Thank you Douglas for this tip,

I have been scouring the web on how to do exactly this. With no luck I gave up, months later while researching SQL stuff, I stumbled upon this link and it was exactly what I needed months ago. Now I have this knowledge under my belt all thanks to you. Thank you!


Tuesday, December 30, 2014 - 6:52:29 PM - Payam Back To Top

Thank you so much! It really helped me and that was what I was searching for... :) Have a nice day.


Tuesday, December 02, 2014 - 3:39:42 AM - Athirah Back To Top

HI Douglas. This is a great code. Howevder, I get this error :

Conversion failed when converting the varchar value '; ' to data type int. 

I've tried everything and still its not working.


Friday, November 07, 2014 - 12:59:06 AM - Kavin Back To Top

I want for real number the stuff command works only for variables


Thursday, November 06, 2014 - 2:35:56 PM - Luna Back To Top

This is exactly what I have been searching for weeks! Thank you. Works perfectly with SSMS.


Saturday, October 25, 2014 - 2:07:43 AM - Dr Mohamed Bekheit Back To Top

Dear Author Castilho,

Thank you for your useful tips published online.

I have one simple question that is bothering me so much, since I am a physician and have no big experience in sql (not even small) .

I need to recover data from our web based database. There are multiple records with the same name (because we repeat experiments for the same subject, but each experiment is in a separate row), so there will be several rows concerning one person but containes different data. I need while exporting the data to have a single row for each record that contains all information.

I will copy to you the automated query generated by the interface:

SELECT patient.nom AS 'Nom', patient.poids AS 'Poids', biology.date_consultation AS 'Measurement date', biology.heure_GDS AS 'Hour', biology.timemesure AS 'Time of measurement (before hpx ...)', biology.jour AS 'Day (J-7/J0 ...)', biology.chk_blood_type1 AS 'Arterial Blood', biology.pCO2 AS 'pCO2 mmHg', biology.pO2 AS 'pO2 mmHg', biology.SO2 AS 'sO2 %', biology.Hb AS 'Hb g/dL', biology.Htc AS 'Htc %', biology.Na AS 'Na mmol/L', biology.K AS 'K mmol/L', biology.Ca AS 'Ca mmol/L', biology.Lac AS 'Lactates mmol/L', biology.Glu AS 'Glu (glycemia)', biology.chk_blood_type2 AS 'Venous Blood', biology.pCO2_vein AS 'pCO2 mmHg', biology.pO2_vein AS 'pO2 mmHg', biology.SO2_vein AS 'sO2 %', biology.pH_vein AS 'pH val abs', biology.Hb_vein AS 'Hb g/dL', biology.Htc_vein AS 'Htc %', biology.Na_vein AS 'Na mmol/L', biology.K_vein AS 'K mmol/L', biology.Ca_vein AS 'Ca mmol/L', biology.Lac_vein AS 'Lactates mmol/L', biology.Glu_vein AS 'Glu (glycemia)', biology.chk_blood_type3 AS 'Mixt Venous Blood', biology.pCO2_veinMix AS 'pCO2 mmHg', biology.pO2_veinMix AS 'pO2 mmHg', biology.SO2_veinMix AS 'sO2 %', biology.pH_veinMix AS 'pH val abs', biology.Hb_veinMix AS 'Hb g/dL', biology.Htc_veinMix AS 'Htc %', biology.Na_veinMix AS 'Na mmol/L', biology.K_veinMix AS 'K mmol/L', biology.Ca_veinMix AS 'Ca mmol/L', biology.Lac_veinMix AS 'Lactates mmol/L', biology.Glu_veinMix AS 'Glu (glycemia)', biology.date_consult_labo AS 'Measurement date', biology.heure_labo AS 'Hour', biology.timelabo AS 'Time of measurement (before hpx ...)', biology.jourlabo AS 'Day (J-7/J0 ...)', biology.NA_labo AS 'Na mmol/L', biology.K_labo AS 'K mmol/L', biology.Cl AS 'Cl mmol/L', biology.CO2_labo AS 'CO2', biology.Creatinine AS 'Creatinine µmol/L', biology.Uree AS 'Uree mmol/L', biology.Glycemie AS 'Glycemie mmol/L', biology.Lactates AS 'Lactates mmol/L', biology.Proteines AS 'Proteines g/L', biology.ASAT AS 'ASAT Ul/l', biology.ALAT AS 'ALAT Ul/l', biology.Bili_tot AS 'Bilirubine totale µmol/L', biology.Bili_conj AS 'Bilirubine conj µmol/L', biology.Bili_lib AS 'Bilirubin libre µmol/L', biology.Gamm_GT AS 'Gamma GT Ul/l', biology.Phosphatase_Alcaline AS 'Phosphatase Alcaline Ul/l', biology.Hemoglo AS 'Hemoglobine g/dl', biology.Hemato AS 'Hematocrite %', biology.Leuco AS 'Leucocytes -/mm3', biology.Plaquettes AS 'Plaquettes -/mm3', biology.TP AS 'TP (taux de prothrombine) %', biology.timeTP AS 'time TP (sec)', biology.timeTPtemoin AS 'time temoin TP (sec)', biology.TCA AS 'TCA (temps de cephaline active) val abs', biology.timeTCA AS 'TCA time (sec)', biology.timeTCAtemoin AS 'TCA time temoin (sec)', biology.INR AS 'INR (internation normalized ratio) val abs', biology.Ammo AS 'Ammoniemie µmol/L'

FROM patient, biology

 

WHERE biology.ID_patient = patient.ID_patient


Thursday, October 23, 2014 - 4:45:41 PM - Slow roll Back To Top

--This is a great time saver and great code- thank you!

 


Wednesday, October 22, 2014 - 10:59:54 AM - Lisa Back To Top

 

 

Dude you just increased my quality of life!!!!!


Wednesday, October 15, 2014 - 3:57:59 AM - John Back To Top

Excellent post, thanks very much for sharing.


Tuesday, October 07, 2014 - 4:05:58 PM - Ygg Meanhorse Back To Top

Very well done artical. I learned some stuff. Thanks a lot.


Monday, September 29, 2014 - 10:40:19 PM - po Back To Top

Thank you so much and your article is so helpful but I have 1 question. 

I wrote an SQL statement with STUFF and FOR XML function and try to put it into MS Query in Excel, but I found it's not working.  Is it STUFF and FOR XML function is not compatable in excel ms query?  If so, are there any other SQL functions I can use to achieve the same results and is compatable in excel ms query?

Thanks a lot for your help. 

Regards,

Po


Wednesday, July 23, 2014 - 10:50:51 PM - Zaigham Back To Top

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 :-)


Tuesday, June 24, 2014 - 1:23:09 PM - Douglas Back To Top

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.


Tuesday, June 24, 2014 - 3:23:22 AM - sunil Back To Top

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


Thursday, May 22, 2014 - 9:47:19 AM - Parimal Back To Top

That was very helpful :) Thanks for that :)

 

Best regards,

Parimal


Tuesday, April 08, 2014 - 6:52:14 AM - Suganya Back To Top

Great...It was very helpful


Monday, March 24, 2014 - 3:23:50 AM - Satish Back To Top

Thank you Douglas for the nice article !


Thursday, January 23, 2014 - 9:15:19 PM - Douglas P. Castilho Back To Top

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!
 

Thursday, January 23, 2014 - 8:32:13 PM - Douglas P. Castilho Back To Top

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, December 19, 2013 - 2:42:11 AM - Alekhya Back To Top

 

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.... 


Friday, December 13, 2013 - 4:30:46 PM - rodrigo Back To Top

Great article!

 

 


Saturday, November 02, 2013 - 11:54:26 AM - talal Back To Top

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


Saturday, October 19, 2013 - 6:13:29 AM - Baiju Back To Top
  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


Thursday, October 03, 2013 - 2:06:14 PM - saadee Back To Top

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


Tuesday, August 13, 2013 - 11:14:44 AM - Douglas P. Castilho Back To Top

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, August 08, 2013 - 12:48:27 PM - alotus Back To Top

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!


Thursday, August 08, 2013 - 12:38:32 PM - Miles Back To Top

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?


Wednesday, July 31, 2013 - 9:59:43 AM - tCubeR Back To Top


--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)

*/

 


Tuesday, July 16, 2013 - 6:19:24 AM - priya Back To Top

 

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

 


Friday, July 12, 2013 - 12:47:06 PM - Brian Nordberg Back To Top

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.


Friday, July 05, 2013 - 1:21:03 AM - Banwari Back To Top

 

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 


Thursday, July 04, 2013 - 11:07:17 AM - Douglas P. Castilho Back To Top

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!
 

Thursday, July 04, 2013 - 10:22:56 AM - Banwari Back To Top

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

How can I get this result?


Thursday, May 30, 2013 - 10:04:52 AM - Nikheel Back To Top

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,


Sunday, May 26, 2013 - 2:47:56 PM - David Back To Top

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.


Saturday, May 25, 2013 - 8:56:59 AM - Javed Back To Top

 

 

Hi,

I agree with this example 

  thank's for clarify my doubt


Monday, May 06, 2013 - 1:39:37 PM - sunil kumar jain Back To Top

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

 


Tuesday, April 30, 2013 - 5:21:50 PM - leon jhirad Back To Top

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, April 15, 2013 - 12:44:40 PM - thomasD Back To Top

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!


Saturday, April 06, 2013 - 7:10:58 PM - JustpassingBy Back To Top

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 



 


Saturday, April 06, 2013 - 3:04:40 PM - Douglas Back To Top

Hi LadyRuna.

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


Saturday, April 06, 2013 - 2:57:32 PM - Douglas Back To Top

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


Friday, April 05, 2013 - 5:23:52 PM - LadyRuna Back To Top

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.  :) 


Friday, April 05, 2013 - 3:09:26 PM - Greg Robidoux Back To Top

@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 - 1:19:55 PM - Joe Celko Back To Top

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. 


Learn more about SQL Server tools