SQL Server Cross Tab with String and Groups

Posted on 2004-08-26
Last Modified: 2008-03-17
Hi, if i have a table like this:

ID  Name  Group
1   Tom    A
2   Joan    B
3   Bush    B
4   Joe      A

How can I get the following result?

Group  Name_String
A  TomJoe
B  JoanBush
Question by:mozzie_c
  • 3
  • 2
LVL 75

Accepted Solution

Anthony Perkins earned 100 total points
ID: 11909811
First do (untested):

CREATE FUNCTION udf_GetNames(@Group as char(1))

Returns varchar(200)

Declare @Names varchar(200)

Set @Names = ''

Select @Names = @Names + Name
From Table1
Where [Group] = @Group

Return @Names

Select dbo.udf_GetNames([Group]
From Table1
Group By [Group]

Expert Comment

ID: 11910294
This is a full blown crosstab function tweaked from other web forums for my app.

CREATE procedure p_CrossTab
     @tablename               varchar(255),     -- Table/View on which to perform the cross tab query.
     @crosscolumn          varchar(255),     -- Attribute to be used as columns in the cross tab.
     @crossrow               varchar(255),     -- Attribute to be used as rows in the cross tab.
     @crossvalue               varchar(255),     -- Attribute to be used as value in the cross tab.
     @sumfunc                  varchar(20)

-- Work variables
     @sql                    varchar(8000),     -- Hold the dynamically created sql statement
     @colname               varchar(255),     -- The current column when building sql statement
     @i                         smallint,          -- know when we reached the last column (@i = @cols)
     @cols                    smallint,          -- Number of columns
     @longest_col          smallint,          -- the len() of the widest column
     @CrLf                    char(2)
-- Constants
     @max_cols_in_table     smallint,
     @max_col_name_len     smallint,
     @max_statement_len     smallint,
--     @sql7                    bit,               -- 1 when version 7, 0 otherwise.
     @err_severity          int

set nocount on

set @max_cols_in_table     = 255
set @max_statement_len     = 8000
set @max_col_name_len     = 128
set @err_severity          = 11
set @CrLf = char(13) + char(10)

-- Check inputs
if @tablename is null or @crosscolumn is null or @crossrow is null or @crossvalue is null begin
     raiserror ('Missing parameter(s)!',@err_severity,1)
     return 0

-- Don't check for columns because we may actually get an expression as the column name

-- prepare for future feature of checking database version to validate
-- inputs. Default to version 7
--set @sql7 = 1
--if (patindex('%SQL Server  7.%',@@version) = 0) begin
--     set @sql7 = 0

-- Extract all values from the rows of the attribute
-- we want to use to create the cross column. This table
-- will contain one row for each column in the crosstab.
create table #crosscol (crosscolumn varchar(255))
set @sql =     ' insert #crosscol Select Distinct ' + @crosscolumn +
               ' From ' + @tablename --+
               --' Group By ' + @crosscolumn
--print @sql
exec (@sql)
set @cols = @@rowcount

if @cols > @max_cols_in_table begin
     raiserror ('Exceeded maximum number of columns in Cross-tab',@err_severity,1)
     return 0
else begin
     if @cols = 0 begin
          raiserror ('Could not find values to use for columns in Cross-tab',@err_severity,1)
          return 0
     else begin
          -- Check if any of the data is too long to make it a name of a column
          select @longest_col = max(len(convert(varchar(129),crosscolumn)))
          from #crosscol

          if @longest_col > @max_col_name_len begin
               raiserror ('Value for column name exceeds legal length of column names',@err_severity,1)
               return 0
          else begin
               -- All Validations OK, start building the dynamic sql statement

               set @sql = ''
               -- Use tmp table rows to create the sql statement for the crosstab.
               -- each row in the table will be a column in the cross-tab
               --set @sql = 'select isnull(convert(varchar(255), ' + @crossrow + '),''Undefined'') As '
               --               + @crossrow + ', ' + @CrLf + space(4)
               set @sql = 'select ' + @crossrow + ', ' + @CrLf + space(4)

               declare cross_sql cursor for
               select crosscolumn
               from #crosscol
               order by crosscolumn

               --print 'Sql cross statment: ' +  @sql

               open cross_sql
               fetch next from cross_sql into @colname
               -- Use "@i" to check for the last column. We need to input commas
               -- between columns, but not after the last column
               set @i = 0
               while @@FETCH_STATUS = 0 begin
                    set @i = @i + 1
                    set @colname = isnull(@colname,'Undefined')
                    set @crossvalue = isnull(@crossvalue, 0)

                    Set @sql = @sql  + '''' +  
                         convert(varchar(128), @colname) +
                         ''' = ' + @sumfunc + '(case convert(varchar(128), ' + @crosscolumn + ')'
                              + char(13) + char(10) + space(8) +
                              ' when ''' + @colname + ''' then ' + @crossvalue + ' else 0 end) '

                    if @i < @cols
                         set @sql = @sql + ', ' + @CrLf + space(4)
                         set @sql = @sql + @CrLf

                    fetch next from cross_sql into @colname

               close cross_sql
               deallocate cross_sql

               set @sql = @sql + ' from ' + @tablename + ' Group By ' + @crossrow
               if len(@sql) >= @max_statement_len begin
                    raiserror ('Crosstab sql statement cannot exceed 7999 characters',@err_severity,1)
                    return 0

               exec (@sql)
               --Select 'Sql' =  @sql
               set nocount off              
               return 1



And here's an example call :

select ... into #temp from ...

exec p_CrossTab '#temp', 'LicenceCode', 'LocationName,CustodianName,AssetDesc,SerialNumber', '1', 'sum'

drop table #temp


Author Comment

ID: 11937583
For the answer from 'acperkins',

How can i use a loop to go through all the groups??
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline


Author Comment

ID: 11937752
OK i've made the loop. Thx heaps!
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11939002
>>How can i use a loop to go through all the groups??<<
You do not need to "loop to go through all the groups" that is what the following SQL Statement does:

Select dbo.udf_GetNames([Group]
From Table1
Group By [Group]

Author Comment

ID: 11948607
hey thx heaps!! it simplified my stored procedure so much!!! =)

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now