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
Solved

SQL Select Query problems

Posted on 2016-10-11
10
49 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
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 48

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 48

Expert Comment

by:Vitor Montalvão
ID: 41838324
No, it's just bringing columns from Table1 (T1).Qtys are from Table2 (T2).
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 28

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 28

Accepted Solution

by:
Pawan Kumar earned 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

829 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