Column related Row values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Column related Row values

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-04 : 23:26:24

Hi,
Am again posting a new method to fetch the Column related row values..

TableA_100: (Here 100 is the Particular Section ID)
Is dynamically created in our product. Where it consists of ID and all Questionarie columns which is dynamically created from another table.

TableB: (Questionarrie)
[SectionID] [GID] [QID] [SQID] [Questions]
100 11 A a Q1
100 11 B b Q2
100 11 C c Q3

From Table B--> Table A columns created dynamically
TABLE A:
[ID] [100X11XBXb] [100X11XBXb] [100X11XCXc]
1 ANS1 ANS2 ANS3
2 ANS8 ANS9 ANS11

From above the tables, when we choose the Section, from the selected section we can able to get the Column names from TableA:

[COLUMN] --> ID,[100X11XBXb],[100X11XBXb],[100X11XCXc]

Need this column names as rows and its corresponding Questions must be populated.

Expecting Result
----------------

[COLUMN] [QUESTION]
[100X11XBXb] Q1
[100X11XBXb] Q2
[100X11XCXc] Q3

Above result column names comes from TABLE:A and the Question names from TABLE:B.

Please help me to get the solution with a QUERY.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 10:19:39

can you explain how you merged data from two table to get that output. i cant see any columns by which you can relate to get the desired output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-05 : 10:53:22

Am getting Table A: columns as Rows.
And From Table B: Column Question can fetched. Using SecctionID, GID,QID,SQID.

Sorry am not giving the solution. I need the solution which i mentioned from the two tables.

Table A: Columna into Rows
Table B: Questions into the row values selected from Table A Columns.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 11:05:13

how question can be fetched from TableB? I cant see any relation of values in it with table A

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-01-05 : 11:31:05

Sir, I have clearly explained that.
In Table B:
SECTIONID, GID, QID, SQID, QUESTIONS were the columns available.

In TableA: the columns will be Concatenated with TableB's those 4 columns with X seperated values.

100X11XAXa, 100X11XBXb, 100X11XCXc

Sorry on my first post I have entered wrongly the first column. In case of AXa i have mentioned as BXb.

Now you got my question.

Regards,
Kalaiselvan R
Love Yourself First....

FischMan2
Yak Posting Veteran

59 Posts

Posted - 2012-01-05 : 11:41:02

quote:
Originally posted by Kalaiselvan

Sir, I have clearly explained that.
In Table B:
SECTIONID, GID, QID, SQID, QUESTIONS were the columns available.

In TableA: the columns will be Concatenated with TableB's those 4 columns with X seperated values.

100X11XAXa, 100X11XBXb, 100X11XCXc

Sorry on my first post I have entered wrongly the first column. In case of AXa i have mentioned as BXb.

Now you got my question.

Regards,
Kalaiselvan R
Love Yourself First....

Can't you just pivot? Using COALESCE to generate the column headers? I wrote something similar yesterday.

FischMan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-05 : 12:15:37

quote:
Originally posted by Kalaiselvan

Sir, I have clearly explained that.
In Table B:
SECTIONID, GID, QID, SQID, QUESTIONS were the columns available.

In TableA: the columns will be Concatenated with TableB's those 4 columns with X seperated values.

100X11XAXa, 100X11XBXb, 100X11XCXc

Sorry on my first post I have entered wrongly the first column. In case of AXa i have mentioned as BXb.

Now you got my question.

Regards,
Kalaiselvan R
Love Yourself First....

Ok makes sense now

for that you need to first unpivot and then join

something like


SELECT m.[COLUMN],b.[Questions]
FROM
(
SELECT [COLUMN] 
FROM TableA a
UNPIVOT ([ANS] FOR [COLUMN] IN ([100X11XBXb], [100X11XBXb], [100X11XCXc]))u
)m
INNER JOIN TableB b
ON CAST(b.[SectionID] AS varchar(20)) + 'X'
 CAST([GID] AS varchar(20)) + 'X'
 CAST([QID] AS varchar(20)) + 'X'
 CAST([SQID]  AS varchar(20))= m.[COLUMN]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources