Skip to main content

SQL query - Table rows as columns



-- Create a table variable to store user data
DECLARE @myTable TABLE
(
   UserName VARCHAR(50),
   ArticleName VARCHAR(50)
)

-- Insert some data to table to work on that data
INSERT INTO @myTable(UserName, ArticleName)
VALUES ('Jack', 'ASP.NET')
INSERT INTO @myTable(UserName, ArticleName)
VALUES ('Jack', 'SQL Server')
INSERT INTO @myTable(UserName, ArticleName)
VALUES ('Jack', 'C#')
INSERT INTO @myTable(UserName, ArticleName)
VALUES ('Jack', 'VB.NET')

INSERT INTO @myTable(UserName, ArticleName)
VALUES ('David', 'Java')
INSERT INTO @myTable(UserName, ArticleName)
VALUES ('David', 'Java Beans')
INSERT INTO @myTable(UserName, ArticleName)
VALUES ('David', 'Java script')

SELECT UserName, ArticleName FROM @myTable

-- This is how the table looks after inserting the data

Now I want all the articles related to Jack and David in a single column.
This how we can achieve this
-- Cross join each user with his article. By cross joining we will get all the articles for each user
SELECT DISTINCT A.UserName,Articles FROM @myTable A CROSS APPLY ( -- Now get all the articles for each author in XML SELECT ArticleName + ', ' FROM @myTable B WHERE A.UserName = B.UserName FOR XML Path('') ) AS C (Articles)
The output of the below query is shown below.
-- By applying cross join I can able to get all the articles related with Jack and David.

Comments

Post a Comment

Popular posts from this blog

UML - Association, Aggregation, Composition, Generalization, Specialization, Realization and Dependency

Association Association is a simple relationship between two classes. For example A relationship between Professor Class and Student class is known as Association. Both Classes can exist without each other, so Professor and Student are two independent classes. In this kind of relationships there will not be any owner class. Both classes have their own life cycle. UML Notation:     Aggregation Aggregation is a special type of Association. It is known as “Has-A” relationship. For example A Department class can contain Professor Class. Here Department class is owner class. Here in this relationship even after deleting Department class, Professor Class can exits. UML Notation: Composition Composition is a special type of Aggregation. It is known as “Is-A” relationship. For example A University Class has many Department class. Here University and Department objects are dependent on each other. If we delete Univ...
1 comment
Read more

C# Generic Factory

Implement Factory pattern using generics     public interface IDoWork   {       string DoWork();   }     Declare an Interface first by abstracting the common  functionality    Here I am taking the example of DoWork     public class Manager : IDoWork   {     public string DoWork()     {         return "Manager Manages School" ;     }   }     Implement the IDoWork in concrete classes as shown      public class Teacher : IDoWork     {         public string DoWork()         {             return "Teacher teaches student in school" ;         ...
7 comments
Read more

SSIS package - set oledb connections at runtime

SSIS oledb connection manager does't allow user to connect to different oledb servers. If we store oledb connection sting in Config file then it will accepts server name, InitialCatalog and username. But it wont accept password. The is because oledb connection manager has not exposed the oledb password property. To over come this problem we have to override the OLEDB connection manager itself. So that it accepts connections to different oledb servers. Public Sub Main() Dim vars As Variables Dim oledbConnectionManager As ConnectionManager oledbConnectionManager = Dts.Connections("<oledb connection name>") ' ADDED TO MAKE oledb CONNECTION STRING DYNAMIC Dim oServerName As String Dim oUserName As String Dim oInitialCatalog As String Dim oPassword As String Dts.VariableDispenser.LockOneForWrite("oServerName", vars) oServerName = vars("oServerName").Value.ToString().Trim() vars.Unlock() Dts.VariableDispenser.LockOneForWrite("oInitia...
Post a Comment
Read more