nvarchar(max)

Question

nvarchar(max)

sean48

SSC Enthusiast

Points: 125
More actions
August 6, 2011 at 5:40 am

#241802

I have a table with a column B with the data type nvarchar(MAX). In this column B, I want to store descriptions of various length.
I want this description to be shown as a 'two columns table' on a website and/or in Reporting Services.
For example, I add a descrption as follows: ABS Bumpers in carr. kleur Centrale deurvergr. Dubbele airbag Getint glas Stuurbekrachtiging
and I want it to be shown on a website and/or in a report as follows:
ABS Bumpers in carr. kleur
Centrale deurvergr. Dubbele airbag
Getint glas Stuurbekrachtiging
How do I do this. Who can help me?

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

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

1Ankit1 Ten Centuries Points: 1253 More actions · Answer 1

So what are the values that go into each of the two columns of your website table i.e how do you decide what part of this single column goes into Column1 and what goes to col2? Do you have any special character that separates these values in the single source column?

sean48 SSC Enthusiast Points: 125 More actions · Answer 2

you probably mean that I should separate the values in the nvarchar(max) column by , or ;?

for example: ABS, Airbag, Centr. deurvergr.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Neither. Two values means two columns. Database normalisation rules - columns should be atomic, should contain only one value.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sean48 SSC Enthusiast Points: 125 More actions · Answer 4

Obviously, we don't understand each other, so I'll try it this way.

I created the table Uitvoering

CREATE TABLE [dbo].[uitvoering](

[uitvoering_key] [int] IDENTITY(1,1) NOT NULL,

[uitv_code] [nvarchar](100) NULL,

[model_key] [int] NULL,

[uitv] [nvarchar](75) NULL,

[beschrijving] [nvarchar](max) NULL,

[create_timestamp] [datetime] NOT NULL,

[update_timestamp] [datetime] NOT NULL,

CONSTRAINT [PK_uitvoering] PRIMARY KEY CLUSTERED

(

The column [beschrijving] is a description of the column [uitv], therefore the data type of [beschrijving] is [nvarchar](max) because every description has a different length.

The table uitvoering contains de following rows:

[uitvoering_key] [uitv_code] [model_key] [uitv][beschrijving]

1AUDI A4 4-DRS SEDA - 5P 3938711-ABS; Airbag; CV; Getint glas; Sportstuur

2AUDI A4 ALLROAD 5-DRS SUV - 5P 3990412-ABS; Airbag; CV; Getint glas; Led. stuurwiel; Sportstuur; Verw. voorruit

3AUDI S3 SPORTBACK 5-DRS HATC AMBITION PRO LINE 5P 3963028Ambition Pro LineABS; Airbag; CV; Getint glas

4SMAR FORTWO COUPÉ 3-DRS HATC BASE 2P 39083270BaseABS; Airbag; CV

and so on

In a report and/or on a website I want to represent the column [beschrijving] as follows:

[model_key]

11

[uitv]

-

Beschrijving

ABSAirbag

CVGetint glas

Sportstuur

[model_key]

12

[uitv]

-

Beschrijving

ABSAirbag

CVGetint glas

Led. StuurwielSportstuur

Verw. Voorruit

And so on

Beschrijving like a table with two columns with no further format, such as lines or color.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sean48 (8/6/2011)
Beschrijving like a table with two columns with no further format, such as lines or color.

It Beschrijving is like a table with 2 columns, then it should be a child table with two separate columns, not a single column in the parent table.

If can be displayed in a single line, doesn't matter, it shouldn't be stored like that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sean48 SSC Enthusiast Points: 125 More actions · Answer 6

Thanks so far!

I'll be back another time, maybe tomorrow.

I now have to think how to make it clear what I want.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

I understand what you're trying to do and this is an easy thing to do, Sean. If you would post the data in a readily consumable format (see the first link in my signature line below for how to do that, please), I'd be happy to provide you with a coded answer.

Or, you can apply the methods found in the following article and give it a whirl on your own. Yes, I realize that you'e not trying to pass parameters but the techique for building the result set you want is the same.

http://www.sqlservercentral.com/articles/T-SQL/63003/

--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)