Sql-server – separate data from one column to 2 columns

CREATE TABLE #Data
    (
      ItemNo VARCHAR(10),
      Variant INT,
      SalesCode VARCHAR(30),
      UnitPrice INT
    )
GO
INSERT  INTO #Data
        ( ItemNo, Variant, SalesCode, UnitPrice )
VALUES  ( 'A', 1, 'SRP', 100 ),
        ( 'A', 2, 'SRP', 200 ),
        ( 'A', 1, 'WSP', 50 ),
        ( 'A', 2, 'WSP', 100 ),
        ( 'B', 1, 'SRP', 300 ),
        ( 'B', 2, 'SRP', 400 ),
        ( 'B', 1, 'WSP', 150 ),
        ( 'B', 2, 'WSP', 200 )
GO
SELECT  ItemNo, Variant, 'SRP' AS SalesCode_SRP,
        SUM(CASE SalesCode
              WHEN 'SRP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_SRP, 'WSP' AS SalesCode_WSP,
        SUM(CASE SalesCode
              WHEN 'WSP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_WSP
FROM    #Data
GROUP BY ItemNo, Variant 

GO
DROP TABLE  #Data

There are a couple of concepts which need to be distinguished. One is about structure and the other about schema.

Structured data is one where the application knows in advance the meaning of each byte it receives. A good example is measurements from a sensor. In contrast a Twitter stream is unstructured. Schema is about how much of the structure is communicated to the DBMS as how it is asked to enforce this. It controls how much the DBMS parses the data it stores. A schema-required DBMS such as SQL Server can store unparsed data (varbinary) or optionally-parsed data (xml) and fully parsed data (columns).

NoSQL DBMSs lie on a spectrum from no parsing (key-value stores) upwards. Cassandra offers reatively rich functionality in this respect. Where they differ markedly to relational stores is in the uniformity of the data. Once a table is defined only data which matches that definition may be held there. In Cassandra, however, even if columns and families are defined there is no requirement for any two rows in the same table to look anything like each other. It falls to the application designer to decide how much goes in a single row (also referred to as a document) and what is held separately, linked by pointers. In effect, how much denormalisation do you want.

The advantage is you can retrieve a full set of data with a single sequential read. This is fast. One downside is that you, the application programer, are now solely responsible for all data integrity and backward compatibility concerns, for ever, for every bit of code that ever touches this data store. That can be difficult to get right. Also, you are locked into one point of view on the data. If you key your rows by order number, how do you report on the sale on one particular product, or region, or customer?