Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Collections.Generic;
- using System.Data.SqlTypes;
- using System.IO;
- using Microsoft.SqlServer.Server;
- [Serializable]
- [SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
- public struct strconcat : IBinarySerialize
- {
- private List<String> values;
- public void Init()
- {
- this.values = new List<String>();
- }
- public void Accumulate(SqlString value = new SqlString())
- {
- this.values.Add(value.Value);
- }
- public void Merge(strconcat value)
- {
- this.values.AddRange(value.values.ToArray());
- }
- public SqlString Terminate()
- {
- return new SqlString(string.Join(", ", this.values.ToArray()));
- }
- public void Read(BinaryReader r)
- {
- int itemCount = r.ReadInt32();
- this.values = new List<String>(itemCount);
- for (int i = 0; i <= itemCount - 1; i++)
- {
- this.values.Add(r.ReadString());
- }
- }
- public void Write(BinaryWriter w)
- {
- w.Write(this.values.Count);
- foreach (string s in this.values)
- {
- w.Write(s);
- }
- }
- }
- DECLARE @listCol NVARCHAR(2000)
- SELECT @listCol = STUFF(( SELECT '],[' + A.Name
- FROM Attribute A,Category C
- WHERE A.CategoryId = C.Id
- ORDER BY A.DisplayOrder DESC
- FOR XML PATH('')), 1, 2, '') + ']'
- DECLARE @query NVARCHAR(2000)
- SET @query =
- N'SELECT * FROM (SELECT P.*,A.Name AttributeName,PA.OriginalValue FROM Product P,Product_Attribute PA, Attribute A WHERE P.Id = PA.ProductId AND A.Id = PA.AttributeId) src
- PIVOT
- (
- dbo.strconcat(OriginalValue) FOR AttributeName
- IN ('+@listCol+')) AS pvt'
- EXECUTE (@query)
- Msg 406, Level 16, State 1, Line 5
- dbo.strconcat cannot be used in the PIVOT operator because it is not invariant to NULLs.
- [SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000,
- IsInvariantToNulls = true)]
Add Comment
Please, Sign In to add comment