x

Pivot query how to format?

Hello

I have a table"production" with fields of

Date,Shift,Line,Item,Quantity,

How can i get a result mentioned below.

parameters date1,date2


Heading1                Day1       day1     Day1 	        Day2	day2    Day2

Heading2               shift1    shift2   shift3	       shift1   shift2  shift3

result    Item1	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty

      Item2	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty
      Item3	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty
      Item4	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty
      Item5	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty
      Item6	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty
      Item7	 Line&qty   Line&qty Line&qty     Line&qty Line&qty Line&qty

Please Help ME Thanks Andrew

more ▼

asked Nov 05, 2009 at 02:56 PM in Default

user-481 (yahoo) gravatar image

user-481 (yahoo)
1 1 1 1

Before we take a stab and guess the wrong way please let us know the datatypes for the production table. i.e. is Shift binary (0 or 1), numeric (1 or 2) or a character field ('Day', 'Night'). It will assist in coding.
Nov 05, 2009 at 03:57 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
more ▼

answered Nov 06, 2009 at 06:41 PM

Alan Featherston Lago gravatar image

Alan Featherston Lago
97 2 2 2

(comments are locked)
10|1200 characters needed characters left

Sounds like you need something more than just a query. SSRS reports are fairly easy to create

more ▼

answered Nov 06, 2009 at 04:11 PM

dave ballantyne gravatar image

dave ballantyne
928 1 1 4

(comments are locked)
10|1200 characters needed characters left

It seems you want a dinamic table result set, in this way i would suggest using using "WITH CUBE" or "WITH ROLLUP".

Anyway, try using a dynamic table or reporting services to return this query, it should be easier.

more ▼

answered Nov 18, 2009 at 02:00 PM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x369

asked: Nov 05, 2009 at 02:56 PM

Seen: 1492 times

Last Updated: Nov 06, 2009 at 08:22 AM