daily pastebin goal
90%
SHARE
TWEET

Untitled

a guest Jun 13th, 2018 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -------------------   -------------------   -------------------
  2. *     Contact     *   *   ContactRole   *   *      Role       *
  3. -------------------   -------------------   -------------------
  4. * ID              *   * ContactID       *   * ID              *
  5. * Name            *   * RoleID          *   * Name            *
  6. * Address         *   -------------------   -------------------
  7. -------------------
  8.    
  9. declare @Roles nvarchar(max)
  10.  
  11. select @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
  12. from  Role
  13. inner join ContactRole on Role.ID = ContactRole.RoleID
  14. where ContactRole.ContactID = @ContactID
  15.  
  16. select @Roles
  17.    
  18. Select Name, dbo.GetContactRoles(ID) From Contact
  19.    
  20. using System;
  21. using System.Data;
  22. using Microsoft.SqlServer.Server;
  23. using System.Data.SqlTypes;
  24. using System.IO;
  25. using System.Text;
  26.  
  27. [Serializable()]
  28. [SqlUserDefinedAggregate(
  29.     Format.UserDefined,
  30.     IsInvariantToNulls=true,
  31.     IsInvariantToDuplicates=false,
  32.     IsInvariantToOrder=false,
  33.     MaxByteSize=8000)]
  34. public class Concat : IBinarySerialize
  35. {
  36.     #region Private fields
  37.     private string separator;
  38.     private StringBuilder intermediateResult;
  39.     #endregion
  40.  
  41.     #region IBinarySerialize members
  42.     public void Read(BinaryReader r)
  43.     {
  44.         this.intermediateResult = new StringBuilder(r.ReadString());
  45.     }
  46.  
  47.     public void Write(BinaryWriter w)
  48.     {
  49.         w.Write(this.intermediateResult.ToString());
  50.     }
  51.     #endregion
  52.  
  53.     #region Aggregation contract methods
  54.     public void Init()
  55.     {
  56.         this.separator = ", ";
  57.         this.intermediateResult = new StringBuilder();
  58.     }
  59.  
  60.     public void Accumulate(SqlString pValue)
  61.     {
  62.         if (pValue.IsNull)
  63.         {
  64.             return;
  65.         }
  66.  
  67.         if (this.intermediateResult.Length > 0)
  68.         {
  69.             this.intermediateResult.Append(this.separator);
  70.         }
  71.         this.intermediateResult.Append(pValue.Value);
  72.     }
  73.  
  74.     public void Merge(Concat pOtherAggregate)
  75.     {
  76.         this.intermediateResult.Append(pOtherAggregate.intermediateResult);
  77.     }
  78.  
  79.     public SqlString Terminate()
  80.     {
  81.         return this.intermediateResult.ToString();
  82.     }
  83.     #endregion
  84. }
  85.    
  86. SELECT  c.ID, c.Name, c.Address,
  87.     (   SELECT      r.Name + ','
  88.         FROM        "ContactRole" cr
  89.         INNER JOIN  "Role" r
  90.                 ON  cr.RoleID = r.ID
  91.         WHERE       cr.ContactID = c.ID
  92.         ORDER BY    r.ID --r.Name
  93.         FOR XML PATH('')
  94.     ) AS "Roles"
  95. FROM    "Contact" c
  96.    
  97. WITH "Contact" (ID, Name, Address) AS (
  98.                 SELECT 1, 'p1-no role', NULL
  99.     UNION ALL   SELECT 2, 'p2-one role', NULL
  100.     UNION ALL   SELECT 3, 'p3-two roles', NULL
  101. )
  102. , "Role" (ID, Name)AS (
  103.                 SELECT 1, 'teacher'
  104.     UNION ALL   SELECT 2, 'student'
  105. )
  106. , "ContactRole" (ContactID, RoleID) AS (
  107.                 SELECT 2, 1
  108.     UNION ALL   SELECT 3, 1
  109.     UNION ALL   SELECT 3, 2
  110. )
  111.  
  112. SELECT  c.ID, c.Name, c.Address,
  113.     (   SELECT      r.Name + ','
  114.         FROM        "ContactRole" cr
  115.         INNER JOIN  "Role" r
  116.                 ON  cr.RoleID = r.ID
  117.         WHERE       cr.ContactID = c.ID
  118.         ORDER BY    r.ID --r.Name
  119.         FOR XML PATH('')
  120.     ) AS "Roles"
  121. FROM    "Contact" c
  122.    
  123. ID          Name         Address     Roles
  124. ----------- ------------ ----------- ------------------
  125. 1           p1-no role   NULL        NULL
  126. 2           p2-one role  NULL        teacher,
  127. 3           p3-two roles NULL        teacher,student,
  128.    
  129. SELECT Contact.Name as cName, Role.Name as rName FROM Contact
  130. JOIN ContactRole ON (Contact.ID==ContactRole.ContactID)
  131. JOIN Role ON  ON (Role.ID==ContactRole.RoleID)
  132.    
  133. forloop:
  134.     array[ cName ] .= rName.', ';
  135. endforloop;
  136.    
  137. create function dbo.getRole(
  138.     @ContactId int)
  139. returns varchar(8000)
  140. as
  141. begin
  142. declare @Roles varchar(8000)
  143.  
  144. select
  145.     @Roles = case when @Roles is null then '' else @Roles + ', ' end + Role.Name
  146. from Role
  147. inner join ContactRole on Role.ID = ContactRole.RoleID
  148. where ContactRole.ContactID = @ContactID
  149.  
  150. return @Roles
  151.    
  152. SELECT c.id, c.name, dbo.getRole(ID) as Roles
  153. FROM Contact
  154.    
  155. SELECT [<group field 1>], [<group field 2>], [etc...], (
  156.     SELECT CAST([<field to list>] AS VARCHAR(MAX)) +
  157.         CASE WHEN (ROW_NUMBER() OVER (ORDER BY [<inner order-by REVERSED>]) = 1)
  158.             THEN '' ELSE ',' END
  159.         AS [text()]
  160.     FROM [<inner table>]
  161.     WHERE [<inner table join field>] = [<outer table join field>]
  162.     AND [<inner conditions>]
  163.     ORDER BY [<inner order-by>]
  164.     FOR XML PATH('')) AS [<alias>]
  165. FROM [<outer table]
  166. WHERE [<outer conditions>]
  167.    
  168. create function FetchProducts(@orderid int) returns varchar(1000)
  169.     as
  170.     begin
  171.     declare prods cursor for select ProductName from products where
  172.             productid in (select ProductId from [Order Details]
  173.               Where OrderId = @orderid)
  174.  
  175.     open prods
  176.  
  177.     declare @products  varchar(1000)
  178.     declare @cp varchar(500)
  179.     Select @products = ''
  180.     fetch prods into @cp
  181.  
  182.     while @@fetch_status = 0
  183.     begin
  184.         SET @products = @products + ',' + @cp
  185.         fetch prods into @cp
  186.     end
  187.  
  188.     close prods
  189.     deallocate prods
  190.  
  191.     return substring(@products, 2, len(@products)-1)
  192.     end
  193.    
  194. select orderid, orderdate, dbo.FetchProducts(orderid)
  195. from orders where customerid = 'BERGS'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top