Split column values into multiple rows using sql

Question

Post reply

Split column values into multiple rows using sql

sqluserr

Old Hand

Points: 377
More actions
March 29, 2010 at 11:12 am

#222559

NA

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

mlak (3/29/2010)
NA

And your question is?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

mlak (3/29/2010)
NA

Please see the following article...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

sqluserr Old Hand Points: 377 More actions · Answer 3

Sorry the question was not posted Correctly.

I need help with following requirement.

I want data to be generated in the following way in my stored procedure as given in the attachment.

Now i'll explain the DB structure

I have a "User" table with User Key and User Name.

The User can speak more than one language. So we have Language table with language key and Desc.

User is mapped to the Language in the UserLanguage table which has Language and User Keys.

The User can know more than one technology . So we have a technology Table with technology Key and DESC.

similar to language we have user and Technology mapping table.

Similarly we have many other columns with corresponding table and mapping table.

Now i have to write a stored procedure which returns me the data in the attached format.

How can we avoid all the combination of the data that we get and keep only unique rows.

Since i have to show the data in the Excel sheet using C# (am using aspose) ,is there any way that we can code to get the data in the format given.

Please let me know if you need any other details.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

sqluserr (3/30/2010)
Please let me know if you need any other details.

I already did. Please see my previous post on this thread.

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

sqluserr Old Hand Points: 377 More actions · Answer 5

Do you need the sample data and the table structure? 🙂 That is what i see in the link posted by you. I will post it in a minute.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Post ur table structure in form of Create Table, and sample data in form of insert into scripts , image/visual representation of ur desired result, we will provide u a solution within minutes 😀

nguyennd SSCommitted Points: 1881 More actions · Answer 7

Hic, i try to buil DDL for your question

CREATE TABLE USERS(

USERKEY int,

USERNAME VARCHAR(10)

)

CREATE TABLE LANGS(

LANGKEY int,

LANGNAME VARCHAR(10)

)

CREATE TABLE TECHS(

TECHKEY int,

TECHNAME VARCHAR(10)

)

CREATE TABLE USERLANG(

USERKEY int,

LANGKEY int

)

CREATE TABLE USERTECH(

USERKEY int,

TECHKEY int

)

truncate table USERS

INSERT INTO USERS(USERKEY,USERNAME)

SELECT 1 as USERKEY,'ABC' as USERNAME UNION ALL

SELECT 2 as USERKEY,'123' as USERNAME

truncate table LANGS

INSERT INTO LANGS(LANGKEY,LANGNAME)

SELECT 1 as LANGKEY,'English' as LANGNAME UNION ALL

SELECT 2 as LANGKEY,'Hindi' as LANGNAME UNION ALL

SELECT 3 as LANGKEY,'Sanskit' as LANGNAME

truncate table TECHS

INSERT INTO TECHS(TECHKEY,TECHNAME)

SELECT 1 as TECHKEY,'C#' as TECHNAME UNION ALL

SELECT 2 as TECHKEY,'Sql' as TECHNAME

truncate table USERLANG

INSERT INTO USERLANG(USERKEY,LANGKEY)

SELECT 1 as USERKEY,1 as LANGKEY UNION ALL

SELECT 1 as USERKEY,2 as LANGKEY UNION ALL

SELECT 1 as USERKEY,3 as LANGKEY UNION ALL

SELECT 2 as USERKEY,2 as LANGKEY UNION ALL

SELECT 2 as USERKEY,3 as LANGKEY

truncate table USERTECH

INSERT INTO USERTECH(USERKEY,TECHKEY)

SELECT 1 as USERKEY,1 as TECHKEY UNION ALL

SELECT 1 as USERKEY,2 as TECHKEY UNION ALL

SELECT 2 as USERKEY,2 as TECHKEY

http://unitech.vn