Making random names!

Hi
I have no idea but want to learn it how to make random names with sql server...
I have a table, called table1, for colums Firstname and Last name
I want it to make random names, so much it is possible it can get, in table2 where the colums is named Names
PLEASE HELP :)

knuff--

Regardign this...

knuff wrote:

...I have no idea but want to learn it how to make random names with sql server...

...I say, I am just guessing (and yet I think this is quite possible)...

...I expect that one could use these TSQL functions...

RAND ( [ seed ] )
Returns a random float value from 0 through 1.

ROUND ( numeric_expression , length [ , function ] )
Returns a numeric expression, rounded to the specified length or precision.

CHAR ( int )
A string function that converts an int ASCII code to a character.

...and some string contatenation to get the job done.

That said, I will add that doing something like this is MUCH better suited to middle-tier logic rather than the database, IMHO.

HTH.

Thank you.

--Mark Kamoski

|||Ok... Sounds logical to me what you wrote there i can try out of that

Making a T-SQL Query

I have a table like below(bolds are field names)

R_IdNameQ1Q2Q3Q4Q5

M001Mikeabcde

J001 Johnabcde

P001 Peterabcde

I want results based on above table as below. The columns (Q1 to Q5) are put as Question_id for each user like M001 and Question (a, b ,c,d,e ) as column Question exactly as below.

Could anyone help me please writing the script to achieve below from above table.

Result

R_idNameQuestion_idQuestion

M001MikeQ1a

M001MikeQ2b

M001MikeQ3c

M001MikeQ4d

M001MikeQ5e

J001JohnQ1a

J001JohnQ2b

J001JohnQ3c

J001JohnQ4d

J001JohnQ5e

........ ....... ....... ....

Thanks all

If you use sql server 2005,

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select

R_Id

,Name

,Question_id

,Question

From

#Data

UNPIVOT

(

Question For Question_idin

([Q1],[Q2],[Q3],[Q4],[Q5])

) UPVT

|||

Assuming you are using SQL 2005, you need to use the UNPIVOT operator.

The code will be something like this:

Code Snippet

DECLARE @.MyTable table
( R_Id varchar(10),
Name varchar(20),
Q1 char(1),
Q2 char(1),
Q3 char(1),
Q4 char(1),
Q5 char(1)
)

INSERT INTO @.MyTable VALUES ( 'M001', 'Mike', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'J001', 'John', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'P001', 'Peter', 'a', 'b', 'c', 'd', 'e' )

SELECT
R_ID,
Name,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

R_ID Name Question Response
- -- --
M001 Mike Q1 a
M001 Mike Q2 b
M001 Mike Q3 c
M001 Mike Q4 d
M001 Mike Q5 e
J001 John Q1 a
J001 John Q2 b
J001 John Q3 c
J001 John Q4 d
J001 John Q5 e
P001 Peter Q1 a
P001 Peter Q2 b
P001 Peter Q3 c
P001 Peter Q4 d
P001 Peter Q5 e

|||

If you use sql server 2000

Code Snippet

Create Table #data (

[R_Id] Varchar(100) ,

[Name] Varchar(100) ,

[Q1] Varchar(100) ,

[Q2] Varchar(100) ,

[Q3] Varchar(100) ,

[Q4] Varchar(100) ,

[Q5] Varchar(100)

);

Insert Into #data Values('M001','Mike','a','b','c','d','e');

Insert Into #data Values('J001','John','a','b','c','d','e');

Insert Into #data Values('P001','Peter','a','b','c','d','e');

Select R_Id,Name,'Q1' Question_id,[Q1] Question From #Data

Union All

Select R_Id,Name,'Q2' Question_id,[Q2] Question From #Data

Union All

Select R_Id,Name,'Q3' Question_id,[Q3] Question From #Data

Union All

Select R_Id,Name,'Q4' Question_id,[Q4] Question From #Data

Union All

Select R_Id,Name,'Q5' Question_id,[Q5] Question From #Data

|||

Arnie, Missed column...

SELECT
R_ID,
Name,
Response,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt

Making a report smaller...?

Alright. I'm stuck. I admit it!

I have a bunch of names, and each name can have one or more 'roles'(operator, reader, key operator, etc. Just random words really.) attached to it.

Using reporting services, I've managed to get the information I need with relative ease... the only problem is, with 900 some records to display, it's current length of 41 pages with just one column going down the left side of each page is not exactly preferred by my superior (can't say I blame him really. Looks kind of odd!)

It looks like this right now:

Name1

Function

Function

Function

Name2

Function

Function

Name3

Function

Function

etc all the way down to page 41

I need it to look something like this:

Name 1 Name 4 Name 7

Function Function Function

Name 2 Name 5 Function

Function Function Name 8

Function Function Function

Name 3 Name 6 Function

Function Function Function

etc. Or some variation of...

I've fiddled around, and merely adding one extra column to the initial table-layout with the same =(!UserName etc) just merely replicates the data in the second column... not giving me the new stuff.

I'm quite new to reporting services, but none of the tutorials I've seen/done seem to accomodate for this... Heeelp!

How about this:

Select the first third of the data as a field for your dataset (you'll need some index field in your data to indicate how much data you've selected). Call it say, Column1.

Select the second and third part of the data similarly as respective fields accordingly. Call these Column 2 and Column 3.

Then set up a table or matrix with fields for Column 1 2 and 3.

|||

You can put your table into multiple columns on the page by:

From the main menu, select report|report properties

select Layout tab

Change the columns to 3

type 0in for spacing and .5in for the margins

check it out in preview

these instuctions are from the book SSRS2005 by Brian Larson, get your own copy from Amazon

|||

Awesome. That does the job right there.

Thanks for the help! Both of you

Make SQL Profiler *NOT* show events with blank application names?

I want SQL Profiler to show me events for 1 specific application. Currently,
I can get it to show me events for that application along with all events
that have no application name. I've tried having just an ApplicationName
LIKE <my-application-name> filter (no NOT LIKE filter), or having an
ApplicationName LIKE <my-application-name> and NOT LIKE '% %' (I couldn't
get it to take just a blank), but I still get events for applications with
no name. Any other ideas? Thanks.
Rick
--
Rick Genter
Sr. Software Engineer
Silverlink Communications
rgenter@.silverlink.com.REMOVERick Genter (rgenter@.silverlink.com) writes:
> I want SQL Profiler to show me events for 1 specific application.
> Currently, I can get it to show me events for that application along
> with all events that have no application name. I've tried having just an
> ApplicationName LIKE <my-application-name> filter (no NOT LIKE filter),
> or having an ApplicationName LIKE <my-application-name> and NOT LIKE '%
> %' (I couldn't get it to take just a blank), but I still get events for
> applications with no name. Any other ideas? Thanks.
I've battled with this myself, but without success. I either end with
filtering on something else, or removing events which does not fill
the column.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||That's what I was afraid of. Thanks, Erland.
"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94659E447C26Yazorman@.127.0.0.1...
> Rick Genter (rgenter@.silverlink.com) writes:
> > I want SQL Profiler to show me events for 1 specific application.
> > Currently, I can get it to show me events for that application along
> > with all events that have no application name. I've tried having just an
> > ApplicationName LIKE <my-application-name> filter (no NOT LIKE filter),
> > or having an ApplicationName LIKE <my-application-name> and NOT LIKE '%
> > %' (I couldn't get it to take just a blank), but I still get events for
> > applications with no name. Any other ideas? Thanks.
> I've battled with this myself, but without success. I either end with
> filtering on something else, or removing events which does not fill
> the column.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp