How to format data in a grid view (pivot?)

Question

Post reply

How to format data in a grid view (pivot?)

Meatloaf

SSCrazy

Points: 2564
More actions
October 23, 2015 at 10:32 pm

#309230

Hello, I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.

For example it would look like this:

--------------------------------------------------------------------------------------------------

Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015

--------------------------------------------------------------------------------------------------

Laptop | Tom | Tom | Tom | Avail | Avail

Projector | Avail | Avail | Avail | Avail | Bob

Air Card | Bob | Bob | Bob | Bob | Bob

It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.

Thank you in advance!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

You could use a ranking function in your query to derive a results like below

Product Date Checkout Id Rid

Laptop 2015-01-01 02:00:00 Tom 1

Laptop 2015-01-01 03:00:00 Jim 2

Laptop 2015-01-01 02:00:00 Jim 1

Laptop 2015-01-01 03:00:00 Tom 2

Then select the MAX(RID) for a given day and find out the user. The rest of it is just presentation in a Matrix.

Jayanth Kurup[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Meatloaf (10/23/2015)

Hello, I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.

For example it would look like this:

--------------------------------------------------------------------------------------------------

Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015

--------------------------------------------------------------------------------------------------

Laptop | Tom | Tom | Tom | Avail | Avail

Projector | Avail | Avail | Avail | Avail | Bob

Air Card | Bob | Bob | Bob | Bob | Bob

It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.

Thank you in advance!

Each "cell" in your output is unique because of the item and date. Use MAX for the names for your aggregation and all will be as expected with your PIVOT.

If you want a coded example, do the things in the article at the first link in my signature line below.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Meatloaf SSCrazy Points: 2564 More actions · Answer 3

Hi,

I have been working on this for a bit and have a new challenge. Using the sample data and code below, the result will show InvMax, and A through G.

Is there a way to make all data appear in one column so I would have a row like this:

A,B,C,D,E,F,G, A_InMin,A_InMax,B_InMin,B_InMax,C_InMin,C_InMax,D_InMin,D_InMax,E_InMin,E_InMax,F_InMin,F_InMax,G_InMin,G_InMax,

CREATE TABLE #InvTest

(InvGrp VARCHAR(200),InvCnt int,InvMin int, InvMax int)

GO

-- Sample Records

INSERT INTO #InvTest VALUES('A','400','600','850')

INSERT INTO #InvTest VALUES('B','400', '450', '500')

INSERT INTO #InvTest VALUES('C','600', '800', '1000')

INSERT INTO #InvTest VALUES('D','180', '200', '200')

INSERT INTO #InvTest VALUES('E','325','300','500')

INSERT INTO #InvTest VALUES('F','300', '300','350')

INSERT INTO #InvTest VALUES('G','399', '400','450')

GO

DECLARE @PivotQuery AS NVARCHAR(MAX)

DECLARE @InvGrp AS NVARCHAR(MAX)

SELECT @InvGrp = ISNULL(@InvGrp + ',','')

+ QUOTENAME(InvGrp)

FROM (SELECT DISTINCT InvGrp FROM #InvTest) AS Inv

SET @PivotQuery =

N'SELECT InvMin, InvMax, ' + @InvGrp + '

FROM #InvTest

PIVOT(Max(InvCnt)

FOR InvGrp IN (' + @InvGrp + ')) AS PVTTable'

EXEC sp_executesql @PivotQuery

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

Meatloaf (10/30/2015)

Is there a way to make all data appear in one column so I would have a row like this:

A,B,C,D,E,F,G, A_InMin,A_InMax,B_InMin,B_InMax,C_InMin,C_InMax,D_InMin,D_InMax,E_InMin,E_InMax,F_InMin,F_InMax,G_InMin,G_InMax,

Do you mean one column or one row?

For one row, I would use a cross tabs approach instead of 3 pivots. It will be faster and easier to do.

Cross Tabs Part 1[/url]

Cross Tabs Part 2[/url]

If you need one column (and to understand part of the code that I'm posting) you should read the following article: http://qa.sqlservercentral.com/articles/comma+separated+list/71700/



DECLARE @PivotQuery AS NVARCHAR(MAX)

DECLARE @Columns AS NVARCHAR(MAX)

DECLARE @ColumnsMinMax AS NVARCHAR(MAX)

SELECT @Columns = STUFF((SELECT CHAR(9) + ',MAX( CASE WHEN InvGrp = ''' + InvGrp + ''' THEN InvCnt END) AS ' + InvGrp + CHAR(10)

                         FROM (SELECT DISTINCT InvGrp FROM #InvTest) AS Inv

                         ORDER BY InvGrp

                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

SELECT @ColumnsMinMax = (SELECT   CHAR(9) + ',MAX( CASE WHEN InvGrp = ''' + InvGrp + ''' THEN InvMin END) AS ' + InvGrp + '_InMin' + CHAR(10)

                                + CHAR(9) + ',MAX( CASE WHEN InvGrp = ''' + InvGrp + ''' THEN InvMax END) AS ' + InvGrp + '_InMax' + CHAR(10)

                         FROM (SELECT DISTINCT InvGrp FROM #InvTest) AS Inv

                         ORDER BY InvGrp

                         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

SELECT @PivotQuery = 'SELECT ' + @Columns + @ColumnsMinMax + 'FROM #InvTest;'

EXEC sp_executesql @PivotQuery

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

Meatloaf SSCrazy Points: 2564 More actions · Answer 5

Meatloaf

SSCrazy

Points: 2564

October 30, 2015 at 10:03 am

#1836752

this is so helpful, thank you very much!

Meatloaf SSCrazy Points: 2564 More actions · Answer 6

Meatloaf

SSCrazy

Points: 2564

October 30, 2015 at 10:08 am

#1836756

Hi,

Is it possible to enter this string into a temp table?

SELECT @PivotQuery = 'SELECT ' + @Columns + @ColumnsMinMax + 'FROM Fill_Rate_Summary_Total;'

EXEC sp_executesql @PivotQuery

Luis Cazares SSC Guru Points: 183686 More actions · Answer 7

I'm not sure what you mean. What would be the problem?

INSERT INTO #TempTable(StringColumn) VALUES( @StringVariable);



--OR

INSERT INTO #TempTable(StringColumn) SELECT @StringVariable;

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Luis Cazares (10/30/2015)

I'm not sure what you mean. What would be the problem?

INSERT INTO #TempTable(StringColumn) VALUES( @StringVariable);
--OR
INSERT INTO #TempTable(StringColumn) SELECT @StringVariable;

You mean other than the fact that they're both RBAR??? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions