?
Solved

SQL Select Query problems

Posted on 2016-10-11
10
Medium Priority
?
52 Views
Last Modified: 2016-10-15
Hello EE,

in a table I got columns values Code, Desc01, Desc02, Desc03, Desc04 up to 15

in another table, I got columns:   Code, Qty01, Qty02, Qty03, Qty04 ... all to Qty15.

my question is, I need to read the second table but I dont want to show in headers Qty01 Qty02 ..
I want to show the real description based on the "Code value"

if code is R1
Desc01 to 15 will be let's say:   XXS, XS, S, M, L, XL, XXL, XXXL

if R2 it could be:   0 2 4 6 8 10 12 14 16

i want to show the good QTYS on the real description, without showing "Qty01" "Qty02"  you know what I mean ?

can you help on this,.. ?
0
Comment
Question by:PhilippeRenaud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41838316
SELECT T1.*
FROM Table1 T1
    INNER JOINT Table2 T2 ON T1.Code = T2.Code

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838323
if I do this, I will get columns name as  QTy01, Qty02 Qty03

this is not what I want
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41838324
No, it's just bringing columns from Table1 (T1).Qtys are from Table2 (T2).
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838357
what do you want the output to show?
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838370
If in my, lets call is TableA, I have :

Code    DEsc01   Desc02    Desc03    Desc04
R1         XXS          XS            S              M
R2          0             2               4              6


and in TableB I Got :

Code      QTY01       QTY02        QTY03        QTY04
R1           5                 0                3                  1
R2           1                 6                3                  10


if I do a query that I want to know the Qtys for R1 i woudl like the results to be like this :

XXS      XS       S       M
5          0          3      1
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838382
if you want XXS, XS, ... to be the header, you will need to create a dynamic query.

if you want XXS, XS, ... to be the first row, this is much easier:
select DEsc01,  Desc02,    Desc03,    Desc04, ....
from tableA
where Code = 'R1'
union all
select QTY01,  QTY02,    QTY03,    QTY04, ....
from tableB
where Code = 'R1'

Open in new window




So what do you want?
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41838546
@Author - You want below as column Names?
XXS      XS       S       M
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41839003
yes !
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41839619
Here !! Pls  try..

Table Creation

--

CREATE TABLE table1
(
	 Code  VARCHAR(10)
	,DEsc01  VARCHAR(10) 
	,Desc02   VARCHAR(10) 
	,Desc03   VARCHAR(10) 
	,Desc04 VARCHAR(10)
)
GO

INSERT INTO table1 VALUES
('R1'     ,    'XXS' ,         'XS',            'S',              'M'),
('R2'      ,    '0'      ,       '2'      ,         '4',              '6')


CREATE TABLE table2
(
	 Code  VARCHAR(10)
	,QTY01  INT 
	,QTY02   INT
	,QTY03   INT
	,QTY04 INT
)
GO

INSERT INTO table2 VALUES
('R1'     ,      5 ,                0,                3,                  1),
('R2'     ,      1  ,               6 ,               3 ,                 10)
GO

--

Open in new window


Solution

--


DECLARE @Code AS VARCHAR(10) = 'R1'
DECLARE @Cols AS VARCHAR(1000) = ''

SELECT @Cols = @Cols + '[' + Desc01 + '],[' + Desc02  + '],[' + Desc03 + '],[' + Desc04 + ']' FROM table1 
WHERE Code = @Code

DECLARE @Query AS VARCHAR(MAX) = ''

SET @Query = '

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY code ORDER BY(SELECT 1)) rnk FROM 
	(
		SELECT *  FROM table1			
	)p
	WHERE code = ' + '''' + @code + '''' + ' 
)
SELECT ' +  @Cols +  ' FROM 
(
	SELECT *
	FROM
	(
		SELECT * FROM CTE c 
	)t
	UNPIVOT
	(
		rnku FOR t IN ( Desc01 , Desc02  , Desc03 , Desc04 )
	) AS Unvot
)p PIVOT
(
	MAX(t) FOR rnku IN ( ' +  @Cols  + ' )
)as pvt
WHERE rnk <> 1
UNION ALL
SELECT QTY01, QTY02 , QTY03 , QTY04 FROM table2 
WHERE Code = ' + '''' + @code + ''''

EXEC (@Query)


--

Open in new window



Output for Code = R1

XXS      XS      S      M
5      0      3      1



Output for Code = R2

0      2      4      6
1      6      3      10


Hope it helps !!
0
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 41845008
thanks
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question