Create view with exploding rows into multiple

Question

Post reply

Create view with exploding rows into multiple

NERO

Old Hand

Points: 330
More actions
April 28, 2014 at 1:55 pm

#288271

I have a table like below:
ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
Any thoughts?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

a4apple SSCrazy Points: 2620 More actions · Answer 1

can you show us some sample output...

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

How do you define that you need 2 or 3 lines?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Burrows SSC Guru Points: 65138 More actions · Answer 4

SELECT t.ItemId,l.line,

CASE WHEN l.Line = 1 THEN t.Amount

WHEN t.SrvType IN (1,2) AND l.Line = 2 THEN t.Tax1+t.Tax2

WHEN t.SrvType = 3 AND l.Line = 2 THEN t.Tax1

WHEN t.SrvType = 3 AND l.Line = 3 THEN t.Tax2

END AS [Value]

FROM

t

JOIN (VALUES (1,1),(1,2),(2,1),(2,2),(3,1),(3,2),(3,3)) l (SrvType,Line) ON l.SrvType = t.SrvType

Far away is close at hand in the images of elsewhere.
Anon.