Dynamic PIVOT quey challange

Hi

I have following Table A In which values of LotNo field is not fixed so we cannot write these values to our PIVOT clause in Select statement. Now can any one provide me sucha a query which can generate this PIVOT result in Table B. Not there is a distnict list of Lotno. is availble from a view.

Table A

LotNo.

Pcs

Wt.

12

200

12.212

13

21

16.214

17

23

21.211

18

27

32.212

Table B

LotNo

12

13

17

18

Pcs

200

21

23

27

Weight

12.212

16.214

21.211

32.212

So I want to write a select query wth PIVOT clause which takes Lotno. which is not fixed values (As given in Adventureworks sample). ThisLotno. is based on distinct values from a list of LotNo. which is availble in another Table. Please anyone help me.

Nilkanth Desai

You can use the following querys

1. Using UNION

SELECT
'Pcs',
*
FROM
(SELECT LotNo,Pcs from TableA) Master
PIVOT
(
Sum(Pcs) for LotNo in ([12],[13],[17],[18])
) AS pvt2
Union All
SELECT
'Wt',
*
FROM
(SELECT LotNo,Wt from TableA) Master
PIVOT
(
Sum(Wt) for LotNo in ([12],[13],[17],[18])
) AS pvt2

2. Merging data into Single Table then applying the Pivot

Declare @.Table Table
(
LotNo int,
Type varchar(10),
Value float
)

Insert Into @.Table Select Lotno,'Pcs', Pcs From TableA;
Insert Into @.Table Select LotNo,'Wt', Wt From TableA;

SELECT
*
FROM
(SELECT LotNo,Type,Value from @.Table) Master
PIVOT
(
Sum(Value) for LotNo in ([12],[13],[17],[18])
) AS pvt2

To generate this query dynamically you can see my earlier post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1006316&SiteID=1

|||

Here you go:

-- Create a Temporary Table holding all Values As LotNo, Group, Value
SELECT LotNo AS LotNo, 'Pcs' as Grp, Pcs as Value INTO #T1 FROM TableA
UNION
SELECT LotNo AS LotNo, 'Weight' as Grp, Weight as Value FROM TableA;

-- Declare a Table holding all Values of LotNo
DECLARE @.T2 AS TABLE(LotNo INT);
INSERT INTO @.T2 SELECT DISTINCT LotNo FROM #T1;

-- Construct the Columns for the SELECT and PIVOT-Clause
DECLARE
@.cols AS NVARCHAR(MAX),
@.lotno AS INT,
@.sql AS NVARCHAR(MAX);

SET @.lotno = (SELECT MIN(LotNo) FROM @.T2);
SET @.cols = '';
WHILE @.lotno IS NOT NULL
BEGIN
SET @.cols = @.cols + ', ' + QUOTENAME(@.lotno)
SET @.lotno = (SELECT MIN(LotNo) FROM @.T2 WHERE LotNo > @.lotno)
END
SET @.cols = SUBSTRING(@.cols, 3, LEN(@.cols));

-- Construct the SQL Statement
SET @.sql = 'SELECT Grp, ' + @.cols + '
FROM #T1
PIVOT(MAX(Value)
FOR LotNo IN (' + @.cols + ')) AS P'

-- Run it
EXEC sp_executesql @.sql;

-- Cleanup
DROP TABLE #T1;

sql

Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an amount of 2576MB. In the error logs there is an error on start up of "Warning: unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research I am unable to determine what are the appropriate settings for Min and Max. We have two instances of sQL Server running, with no other applications. One instance is heavy on the r
ead/write and the other instance is an archive, that sees very little action throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer me in the right direction?
Message posted via http://www.sqlmonster.com
Hi
Have a look at
http://support.microsoft.com/default...b;en-us;274750
Make sure you are using the correct version of the OS.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.c om...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com
|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.c om...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com
|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.sqlmonster.com
|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>
|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas

"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an amount of 2576MB. In the error logs there is an error on start up of "Warning: unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research I am unable to determine what are the appropriate settings for Min and Max. We have two instances of sQL Server running, with no other applications. One instance is heavy on the read/write and the other instance is an archive, that sees very little action throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer me in the right direction?
--
Message posted via http://www.sqlmonster.comHi
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
Make sure you are using the correct version of the OS.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com|||Am I to assume that the min memory setting would be set at zero?
--
Message posted via http://www.sqlmonster.com|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an am
ount of 2576MB. In the error logs there is an error on start up of "Warning:
unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research
I am unable to determine what are the appropriate settings for Min and Max.
We have two instances of sQL Server running, with no other applications. One
instance is heavy on the r
ead/write and the other instance is an archive, that sees very little action
throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer
me in the right direction?
Message posted via http://www.droptable.comHi
Have a look at
http://support.microsoft.com/defaul...kb;en-us;274750
Make sure you are using the correct version of the OS.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.droptable.com|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.droptable.com|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.droptable.com|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via droptable.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.com
>|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via droptable.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.com
>

Dynamic images at runtime

I have a report that comes with one main image and 15 other images that needs to be visible as needed at fixed position to the main image. Adding all the images is easy but managing the visibility is not working. What is the best way to do it? I am looking for sample but have come up empty.

When using a url, you can leverage the expression property to have the dynamic image location.

Or are you meaning a dynamic number of images? That would be tough - probably need to build the rdl yourself w/ .net

|||Thanks for replying I don't have the option to use url, the application will an exe, factory safety related one main image and 15 smaller images added as needed in fixed position relative to the original image.|||it's still hard to figure out exactly what you want to do here. can you give more details in order to better help people answer your question?|||What I am trying to do turns out to be complicated but I have found two samples from the Reporting services news group posted by Microsoft employee Bruce Johnson [MSFT] of how to create it. I have an image and I need to add 15 smaller images to that image at runtime. The two samples I found are Simulated Dynamic Visibility.rdl and ConditionallyDisplayAnImage.RDL. These are doing what I have in mind I could not load the rdl into VS Team suites so I have to create mine based on the content of these two files. I had assumed this should be simple task but it comes with tricks which I just found today. Thanks again for your help.|||

This problem is solved the main part was a cursor yea but my code was due by the close of business, the numeric values representing the images were passed by a cursor in a stored proc which is added to the expression. It works in my box, I will know by Monday if it blows. If you know a better solution please let me know.