String Concatenation in SQL Group By clause

I got one problem in Ahmedabad SQLServer UserGroup.

The problem was something like... there are grouped data and requirement was making sum of string collection, means concatenation of string which are in the same group. Here is the question along with solutionSource Table

Field1     Field2
---------- ----------
Group 1    Member 1
Group 1    Member 2
Group 1    Member 3
Group 2    Member 1
Group 2    Member 2
Group 3    Member 1
Group 3    Member 2

Need output as

Field1     Field2
---------- ----------------------------
Group 1    Member 1,Member 2,Member 3
Group 2    Member 1,Member 2
Group 3    Member 1,Member 2

Solution:

SET NOCOUNT ON

--Creating Tables
DECLARE @Fields AS TABLE
(
    Field1 VARCHAR(10),
    Field2 VARCHAR(10)
)

--Inserting some values
INSERT INTO @Fields VALUES ('Group 1','Member 1')
INSERT INTO @Fields VALUES ('Group 1','Member 2')
INSERT INTO @Fields VALUES ('Group 1','Member 3')
INSERT INTO @Fields VALUES ('Group 2','Member 1')
INSERT INTO @Fields VALUES ('Group 2','Member 2')
INSERT INTO @Fields VALUES ('Group 3','Member 1')
INSERT INTO @Fields VALUES ('Group 3','Member 2')

--T-Sql
SELECT Field1, Substring(Field2, 2, LEN(Field2)) AS Field2 FROM
(
    SELECT
        [InnerData].Field1,
        (SELECT  ',' + Field2 FROM @Fields WHERE Field1=[InnerData].Field1 FOR XML PATH('')) AS Field2
        FROM
        (
            SELECT DISTINCT Field1 FROM @Fields
        ) AS [InnerData]
) AS OuterData

Display multiple comma separated value into single column output Part II

Here is the data:

Field1      Field2
----------- --------------------
1           A,B,C
2           A
3           D,G

And we need output as following

Field1               ID
-------------------- ----
1                    A
1                    B
1                    C
2                    A
3                    D
3                    G

Lets create data first.

DECLARE @Fields AS TABLE
(
    Field1 INT,
    Field2 VARCHAR(20)
)

INSERT INTO @Fields VALUES (1,'A,B,C')
INSERT INTO @Fields VALUES (2,'A')
INSERT INTO @Fields VALUES (3,'D,G')

Here is the query for getting expected result.

SET @AnswerXML =
    REPLACE(REPLACE((SELECT
                        Field1, '<Ids><Id value="' + REPLACE(Field2, ',','" /><Id value="') + '" /></Ids>'
                    FROM @Fields
                    FOR XML PATH ('F')
                ), '&lt;', '<'), '&gt;', '>')

SELECT
     Answer.value('Field1[1]', 'BIGINT') as Field1
    ,y.value('@value[1]', 'VARCHAR(1)') AS ID
FROM
    @AnswerXML.nodes('/F') p(Answer)
OUTER APPLY Answer.nodes('Ids/Id') o(y)

Also check Display multiple comma separated value into single column output Part I

Creating comma separated string from Array - LINQ

Its common requirement to create comma separated string from Array. Using extension methods of LINQ we can do it in single line. Lets create Array first from comma separated values, its simple as we just need to split them using Split function.

string strTest = "1,2,4,6";
string[] Nums = strTest.Split(',');

let's create comma separated string from string Array Nums,

Generally people use foreach loop to create the comma separated string,

But here is the single line code using Lambda Expression to create the same thing.

Console.Write(Nums.Aggregate<string>((first, second) => first + "," + second));
//OUTPUT:
//1,2,4,6

Here the Lambda expression which is accepting two argument and returning the string having concatenation along with delimiter.

Querying Selected Items From ListItemCollection Using LINQ

Working with ListItemCollection is general practices and getting single value for that it's like child's play. What if you need the list of items which are selected?

I am sure you probably use foreach loop something like

List<ListItem> selectedList = new List<ListItem>();
foreach(ListItem li in cbList.Items)
{
    if (li.Selected)
        selectedList.Add(li);
}

Or advance user use the functionality of C#2.0 using yield keyword

private IEnumerable<ListItem> GetSelectedItems(ListControl listControl)
{
    foreach (ListItem itm in listControl.Items)
    {
        if (itm.Selected)
            yield return itm;
    } 
}
.
.
.
.
.
.
{
    IEnumerable<ListItem> selectedList = new List<ListItem>();
    selectedList = GetSelectedItems(cbList);
}

And now using LINQ C#3.0

var selectedItems =
    from li in cbList.Items.Cast<ListItem>()
    where li.Selected == true
    select li;

The only difference is the Cast, we need to make ListItemCollection generic so we can use it in LINQ query.

And now lets see using Lambda Expression C#3.0

List<ListItem> selectedItems =
    cbList.Items.Cast<ListItem>()
    .Where(item => item.Selected == true).ToList();

Here also we did cast to get the extension method Where. And in Where condition we write the lambda expression instead of anonymous delegate function.

We can even write extension method on base class ListControl so we can have selected items for all the list control. Lets write one extension method on ListControl

public static List<ListItem> SelectedItems(this ListControl lc)
{
    List<ListItem> selectedItems =
                lc.Items.Cast<ListItem>()
                .Where(item => item.Selected == true).ToList();
    return selectedItems;
}

And get the selected items using our extension method we will just call the SelectedItems method.

CheckBoxList cbList = new CheckBoxList();
cbList.SelectedItems();

GROUP BY GROUPING SETS - KATMAI

IN SQL 2008 KATMAI, there new extension for group by clause. Which is GROUPING SETS. Its provides you to create sets of columns for grouping from you group by column. For example if you have having group on year, month its allow you to group first on year+month then year as single group, which means now you can do multiple grouping in the single query!

Lets see the example. We are having data for Sales. We need sales for each Month as well as each Year. So each year will have again sum of Month. Here is the pictorial representation of last statement.

So our data:



We need this as output:



In SQL 2000/2005 We may need CURSOR or TEMPORARY TABLE or TABLE VARIABLE, and I am sure that will be tedious job. So lets see how we can achieve this functionality in KATMAI using GROUPING SETS. Here is the small and sweet query :)

SELECT 
    S_Year, S_Month, SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year

You can see the new keyword GROUPING SETS which has more then one Group Clause. This indicates that first you do Group on S_Year + S_Month then you Group it on S_Year.

Lets see the output.



You see the NULL in S_Month? That indicates the sum on months for particular Year mean its in the S_Month grouping. And KATMAI provides one function GROUPING which indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified. Read more on span class="keyword">GROUPING. Lets change query to get proper result.

SELECT 
    CASE 
        WHEN 
            GROUPING(S_Month) = 1 THEN 'Year Total: ' + CAST(S_Year AS VARCHAR(5)) 
        ELSE 
            'Month : ' + CAST(S_Month as VARCHAR(3)) 
        END 
    AS 'Grouping',
    
    SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year, ISNULL(S_Month,15);

And lets see the output.



Download the SQL statements from here.

Check the Origional post

Its very useful while creating Reports.

RadGridNamespace is undefined

I got error RadGridNamespace is undefined while working with RadGrid and Themes all to gather.

Problem was, It was not able to find the scripts and related files form Themes folder. The default path for Script and Theme for Rad controls are fixed and which is under /RadControls. It goes to that directory but its obvious and convention says that if we are using Themes and Skins we put all the scripts, images and skins in App_Themes folder.

So we need to override the default Rad control default path property RadControlsDir in OnInit method

protected override void OnInit(EventArgs e)
{
    rgTest.RadControlsDir = string.Format("~/App_Themes/{0}/RadControls/", this.Page.Theme);
}

Note: If you are having multiple RadGrid on same page, then you have to set property RadControlsDir for all the grids in OnInit method.